SQL Server: Specify Column Name as Attribute Using FOR XML

xytpbqjk 于 2023-04-04 发布在 SQL Server

关注(0)|答案(1)|浏览(167)

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.

sql-server

来源：https://stackoverflow.com/questions/15528265/sql-server-specify-column-name-as-attribute-using-for-xml

1条答案

按热度按时间

9gm1akwq1#

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

赞(0）回复(0）举报 2023-04-04

我来回答

SQL Server: Specify Column Name as Attribute Using FOR XML

1条答案

相关问题

热门标签

最新问答