SQL Server：FOR XML PATH - 嵌套/分组

I have data that looks like:

我的数据看起来像：

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

I want to write a T-SQL query in SQL Server to return the data like this:

我想在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>

I've tried returning the query in XML using:

我试过用XML返回查询：

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

But that gives me an Order node for each row (6 in total) vs. just an order node for each orderId (2 in total).

但是这给了我一个每行（总共6个）的Order节点，而不是每个orderId的一个订单节点（总共2个）。

Any ideas?

有任何想法吗？

2 个解决方案

#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')

#2

For completion: here is a solution without subselect, that should perform faster for big tables. Instead it groups the table as many times as there are levels in the XML and identifies the level with GROUPING_ID (see https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx and https://docs.microsoft.com/en-us/sql/relational-databases/xml/use-explicit-mode-with-for-xml) :

完成：这是一个没有subselect的解决方案，应该对大表执行得更快。相反，它将表格分组的次数与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