SQL Server : FOR XML PATH - nesting / grouping

Question

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:

<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:

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).

Any ideas?

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