SQL FOR XML - 将数据输出为元素或属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9927516/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL FOR XML - Outputting data as Elements or as Attributes
提问by CJM
I'm trying to export some data from a database in a particular XML format specified by the customer. The XML I produce will be manipulated (presumably by XSLT) by a 3rd party to produce the final output, but I want to formal my XML as close as I can to that format.
我正在尝试以客户指定的特定 XML 格式从数据库中导出一些数据。我生成的 XML 将由第 3 方操作（大概由 XSLT）以生成最终输出，但我希望我的 XML 格式尽可能接近该格式。
The customer has requested data on each product like so:
客户已请求每个产品的数据，如下所示：
<product id="1234567890123">
<activeState partNumber="A1234567890" shipmentDate="20110518" />
</product>
My existing SQL is:
我现有的 SQL 是：
SELECT SerialNo as id,
PartNo as partNumber,
CONVERT(VARCHAR(8), GETDATE(), 112) AS shipmentDate,
FROM Products
WHERE SerialNo = @SerialNo
FOR XML PATH ('product'), TYPE)
...which renders:
...呈现：
<product>
<id>100000000458</id>
<partNumber>10004905892</partNumber>
<shipmentDate>20120312</shipmentDate>
</product>
I expect that it is easy enough to manipulate this data in XSLT, but purely as an intellectual exercise, I'd like to see how far I could in SQL. My first ambition was to simply express the id as an attribute of product rather than as a child element. The rendering of the activeState element I was going to leave to the XSLT, but clearly, if I can help them on their way, why not do so...
我希望在 XSLT 中操作这些数据很容易，但纯粹作为智力练习，我想看看我在 SQL 中能走多远。我的第一个目标是简单地将 id 表示为产品的属性而不是子元素。activeState 元素的渲染我打算留给 XSLT，但很明显，如果我可以帮助他们，为什么不这样做......
Any suggestions?
有什么建议？
回答by Mikael Eriksson
Use @to create attributes.
使用@创建属性。
select SerialNo as "@id",
PartNo as "activeState/@partNumber",
convert(varchar(8), getdate(), 112) as "activeState/@shipmentDate"
from Products
where SerialNo = @SerialNo
for xml path('product')
