SQL Server 2014如何生成带多子节点的XML输出？

嘿，我懂你现在的困扰——单独生成基础列表的XML没问题，但关联订单表做嵌套输出时，XML里全是转义后的<和>，完全不是想要的清晰嵌套结构。别着急，这事儿很好解决，根本不用REPLACE这种笨办法！

为啥会出现转义？ #

SQL Server默认会把子查询返回的XML结果当成普通字符串处理，所以会自动把<、>这些XML特殊字符转义成实体字符。要让子查询的XML直接嵌入主XML结构，关键就是告诉SQL Server：“这不是字符串，是XML！”

直接可用的解决方案 #

就在子查询的FOR XML语句末尾加上TYPE关键字就行，看修改后的代码：

SELECT 
    ID1 CusID,
    Names CusName,
    Address PrimAddress,
    (SELECT 
         ID2 PRODUCTID,
         ITEM PRODUCTNAME,
         Quantity 
     FROM #TEMP2 Items 
     WHERE ID1 = Customer.ID1 
     FOR XML AUTO, ELEMENTS, TYPE) AS Items
FROM #TEMP1 Customer 
FOR XML AUTO, ELEMENTS

关键点解释 #

• TYPE关键字会让子查询返回XML数据类型，而不是默认的NVARCHAR字符串。这样主查询生成XML时，就会把这个XML节点直接插入父节点里，不会做任何字符转义。
• 我给子查询的结果加了AS Items，这样所有子订单项会被包裹在<Items>节点里，结构比原来更规整，也更贴近你想要的效果。

优化后的输出示例 #

调整后你会得到这样的清晰嵌套XML：

<Customer>
  <CusID>1</CusID>
  <CusName>Jack</CusName>
  <PrimAddress>Main St</PrimAddress>
  <Items>
    <Items>
      <PRODUCTID>1</PRODUCTID>
      <PRODUCTNAME>Curds</PRODUCTNAME>
      <Quantity>20</Quantity>
    </Items>
  </Items>
</Customer>
<Customer>
  <CusID>2</CusID>
  <CusName>Jill</CusName>
  <PrimAddress>Second St</PrimAddress>
  <Items>
    <Items>
      <PRODUCTID>2</PRODUCTID>
      <PRODUCTNAME>Way</PRODUCTNAME>
      <Quantity>30</Quantity>
    </Items>
    <Items>
      <PRODUCTID>3</PRODUCTID>
      <PRODUCTNAME>Curds</PRODUCTNAME>
      <Quantity>40</Quantity>
    </Items>
  </Items>
</Customer>

如果想让子项的节点名称从<Items>改成<Item>（更符合语义），只要把子查询里的表别名改一下就行：

SELECT 
    ID1 CusID,
    Names CusName,
    Address PrimAddress,
    (SELECT 
         ID2 PRODUCTID,
         ITEM PRODUCTNAME,
         Quantity 
     FROM #TEMP2 Item 
     WHERE ID1 = Customer.ID1 
     FOR XML AUTO, ELEMENTS, TYPE) AS Items
FROM #TEMP1 Customer 
FOR XML AUTO, ELEMENTS

这样子节点就会变成<Item>，完美匹配你期望的结构~

内容的提问来源于stack exchange，提问作者Holmes IV