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.

Answer 1

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