SQL Server:FOR XML PATH

本文介绍了SQL Server:FOR XML PATH-嵌套/分组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的数据如下:

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查询以返回这样的数据:

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>

我尝试使用以下方式以XML返回查询:

I've tried returning the query in XML using:

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

但是，这给了我每行一个Order节点(总共6个)，而每个orderId仅仅一个订单节点(总共2个).

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

有什么想法吗?

推荐答案

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