sql-server - examples - sql server select xml format

SQL Server: FOR XML PATH-anidamiento/agrupamiento

Question

Tengo datos que se parecen a

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

Quiero escribir una consulta T-SQL en SQL Server para devolver los datos de esta manera:

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

He intentado devolver la consulta en XML usando:

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

Pero eso me da un nodo de Order para cada fila (6 en total) frente a solo un nodo de orden para cada orderId (2 en total).

¿Algunas ideas?

Answer 1

Para completar: aquí hay una solución sin subselección, que debería funcionar más rápido para tablas grandes. En su lugar, agrupa la tabla tantas veces como haya niveles en el XML e identifica el nivel con GROUPING_ID (consulte https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx y 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

Answer 2

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