SQL Server: Specify Column Name as Attribute Using FOR XML

Question

I need to return a column alias that has spaces in it in XML from SQL Server. Since you can't have spaces in XML column names I'd like to return this alias in a "columnName" attribute. Generally, the XML would need to look like this:

<customers>
 <customer name="Shmo, Joe">
    <over50 columnName="Number of Orders Over 50 Pounds" value="10">
    <cancelled columnName="Orders Cancelled" value="2">
 </customer>
</customers>

The table looks as follows:

|Name     |over50|cancelled
---------------------------
|Shmo, Joe|  10  |    2

If possible, I'd really like to use the "FOR XML" method of XML extraction. Thanks in advance for any insight.

Martin Smith · Accepted Answer · 2013-03-20 15:56:03Z

2

This should do it.

SELECT Name AS [@name],
       'Number of Orders Over 50 Pounds' AS [over50/@columnName],
       over50 AS [over50/@value],
       'Orders Cancelled' AS [cancelled/@columnName],
       cancelled AS [cancelled/@value]
FROM YourTable
FOR XML PATH('customer'), ROOT('customers')

SQL Fiddle

answered Mar 20, 2013 at 15:56

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DerPflug Over a year ago

Shazam! That's exactly it. Thanks a bunch Martin.

Collectives™ on Stack Overflow

SQL Server: Specify Column Name as Attribute Using FOR XML

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related