SQL Server : FOR XML PATH - nesting / grouping
34,339
Solution 1
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')
Solution 2
For completion: here is a solution without subselect, that should perform faster for big tables. Instead it groups the table as many times as there are levels in the XML and identifies the level with GROUPING_ID (see https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx and 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
Related videos on Youtube
Author by
jared
Updated on August 09, 2020Comments
-
jared almost 4 years
I have data that looks like:
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
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>
I've tried returning the query in XML using:
FOR XML PATH ('Order'), root ('Root')
But that gives me an
Order
node for each row (6 in total) vs. just an order node for eachorderId
(2 in total).Any ideas?
-
jared almost 12 yearsThanks Bert. What does 'type' do in the subquery 'for xml path' statement?
-
Bert almost 12 years@jared It means, 'return this as the XML data type.' So in the query above, it's just returning the subquery as a small xml fragment.