SQL Server:FOR XML PATH-嵌套/分组 [英] SQL Server : FOR XML PATH - nesting / grouping

问题描述

我的数据如下:

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-嵌套/分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！