SQL服务器：FOR XML PATH - 嵌套/分组(SQL Server : FOR XML

我有一个看起来像数据：

OrderID CustomerID  ItemID  ItemName
10000   1234        111111  Product A
10000   1234        222222  Product B
10000   1234        333333  Product C
20000   5678        111111  Product A
20000   5678        222222  Product B
20000   5678        333333  Product C

我想写SQL Server中的T-SQL查询返回这样的数据：

<Root>
  <Order>
    <OrderID>10000</OrderID>
    <CustomerID>1234</CustomerID>
    <LineItem>
      <ItemID>11111</ItemId>
      <ItemName>Product A</ItemName>
    </LineItem>
    <LineItem>
      <ItemID>22222</ItemId>
      <ItemName>Product B</ItemName>
    </LineItem>
    <LineItem>
      <ItemID>33333</ItemId>
      <ItemName>Product B</ItemName>
    </LineItem>
  </Order>
  <Order>
    <OrderID>20000</OrderID>
    <CustomerID>5678</CustomerID>
    <LineItem>
      <ItemID>11111</ItemId>
      <ItemName>Product A</ItemName>
    </LineItem>
    <LineItem>
      <ItemID>22222</ItemId>
      <ItemName>Product B</ItemName>
    </LineItem>
    <LineItem>
      <ItemID>33333</ItemId>
      <ItemName>Product B</ItemName>
    </LineItem>
  </Order>
</Root>

我试着在返回使用XML查询：

FOR XML PATH ('Order'), root ('Root')

但是，这给了我一个Order的每一行（共6）对刚刚订购的节点每个节点orderId （2个）。

有任何想法吗？

Answer 1:

select  
    OrderID,
    CustomerID,
    (
        select 
        ItemID,
        ItemName
        from @Orders rsLineItem
        where rsLineItem.OrderID = rsOrders.OrderID
        for xml path('LineItem'), type
    )
from (select distinct OrderID, CustomerID from @Orders) rsOrders
FOR XML PATH ('Order'), root ('Root')

Answer 2:

完成：这里是无子选择一个解决方案，即应该对大表更快。 相反，它的群体如表中有XML水平和标识与GROUPING_ID水平（见多次https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx和https://docs.microsoft.com/en-us/sql/relational-databases/xml/use-explicit-mode-with-for-xml ）：

with rsOrders as (
  select '10000' OrderID, '1234' CustomerID, '111111' ItemID, 'Product A' ItemName union
  select '10000' orderId, '1234' customerID, '222222' itemID, 'Product B' ItemName union
  select '10000' orderId, '1234' customerID, '333333' itemID, 'Product C' ItemName union
  select '20000' orderId, '5678' customerID, '111111' itemID, 'Product A' ItemName union
  select '20000' orderId, '5678' customerID, '222222' itemID, 'Product B' ItemName union
  select '20000' orderId, '5678' customerID, '333333' itemID, 'Product C' ItemName 
)
select case 
         when GROUPING_ID(ItemID) = 0 then 3 
         when GROUPING_ID(OrderID) = 0 then 2 
         else 1 
         end as tag,
       case 
           when GROUPING_ID(ItemID) = 0 then 2 
         when GROUPING_ID(OrderID) = 0 then 1 
         else null
       end as parent,
       null       as 'Root!1',
       OrderID    as 'Order!2!OrderID!element', 
       CustomerID as 'Order!2!CustomerID!element', 
       ItemID     as 'LineItem!3!ItemID!element', 
       ItemName   as 'LineItem!3!ItemName!element'
  from rsOrders
 group by grouping sets ((), (OrderID, CustomerID), (OrderID, CustomerID, ItemID, ItemName))
 order by OrderID, CustomerID, ItemID, ItemName
   for xml explicit, type

文章来源: SQL Server : FOR XML PATH - nesting / grouping