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