Search code examples
sqlsql-server-2008t-sqlfor-xml

SQL Server: Specify Column Name as Attribute and Node Value FOR XML

I need to return a column alias that has spaces in it in XML from SQL Server. XML would need to look like this:

<Records>
    <Record>
      <Field FieldName="PURCHASE_DETAIL_ID">31320</Field>
      <Field FieldName="ORDER_ID">6507</Field>
      <Field FieldName="PRODUCT_ORDER">1</Field>
    </Record>   
</Records>

My table looks like:

PURCHASE_DETAIL_ID  |   ORDER_ID    | PRODUCT_ORDER
----------------------------------------------------
    31320               6507                1

I'd like to use the FOR XML method of XML extraction.

I tried the following, it works nice for single column, but not for multiple.

SELECT  
    'PURCHASE_DETAIL_ID' AS [Field/@FieldName],
    'GOLI' + RTRIM(CONVERT(VARCHAR(10), ol.ID)) AS [Field],
    'ORDER_ID' AS [Field/@FieldName],
    o.ID AS [Field],
    'PRODUCT_ID' AS [Field/@FieldName],
    P.ID AS [Field]
FROM ...
WHERE ...
FOR XML PATH('Record'), ROOT('Records')
Solution

  • This is what you have to do ... 

    SELECT  
    'PURCHASE_DETAIL_ID' AS [Field/@FieldName],
    'GOLI' + RTRIM(CONVERT(VARCHAR(10), ol.ID)) AS [Field],
'',
    'ORDER_ID' AS [Field/@FieldName],
    o.ID AS [Field],
'',
    'PRODUCT_ID' AS [Field/@FieldName],
    P.ID AS [Field]
FROM ...
WHERE ...
FOR XML PATH('Record'), ROOT('Records')