本文介绍了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')
更多推荐
SQL Server:FOR XML PATH
发布评论