This blog gives you some insight on how nested XML structures can be easily built using FOR XML PATH constructConsider the illustration given below
create table parent_child(ParentID varchar(50),ChildID varchar(50))
insert parent_childvalues (‘Parent1′,’Child1’),(‘Parent1′,’Child2’),(‘Parent1′,’Child3’),(‘Parent4′,’Child4’),(‘Parent5′,’Child5’)
The requirement was to built a xml structure as below
The solution will look like below
SELECT
ParentID ,
(SELECT ChildID
FROM parent_child
WHERE ParentID = p.ParentID
FOR XML PATH (”),TYPE) AS [*]
FROM (SELECT DISTINCT ParentID FROM parent_child) p
FOR XML PATH(‘Parent’), type,root(‘Provider’)
If you check the query above, it makes use of the inner query to generate the inner node values within each Parent node. The entire child node values are wrapped inside single instance of Parent mode by using a nested FOR XML construct. The alias * given will make sure all the returned child nodes will be inserted in the same level as the parent node and blank value passed to FOR XML will suppress the insertion of wrapper node for child values to give us the required xml format output.
Now lets look at another example as below
declare @order table
(
OrderID int,
OrderDesc varchar(1000),
OrderDate datetime,
CustomerID int,
ShippedDate datetime,
WebOrder bit
)
declare @OrderItems table
(
OrderItemID int,
OrderID int,
ItemID int,
Qty int
)
declare @Items table
(
ItemID int,
ItemDesc varchar(100),
Unitprice decimal(10,2)
)
declare @Customers table
(
CustomerID int,
CustomerDesc varchar(100)
)
declare @SubItems table
(
SubItemID int,
ItemID int,
SubItemDesc varchar(100)
)
insert @Order
values (100001,’Order 1′,’20130212′,1004,’20130220′,0)
insert @OrderItems
values (10005,100001,123,10),
(10007,100001,112,8)
insert @Items
values(101,’Monitor CRT 18″‘,200),
(110,’Printer Catridge’,112),
(112,’Copier Ink’,20),
(123,’Wireless Mouse’,30)
insert @SubItems
values (118,110,’SP1′),
(123,110,’SP3′),
(130,112,’SP5′),
(140,112,’SP12′),
(144,123,’SP17′)
insert @Customers
values (1002,’ABC inc’),
(1004,’KBC Associates’)
select o.OrderID,
o.OrderDesc,
c.CustomerDesc,
o.OrderDate,
o.ShippedDate,
o.WebOrder,
oi.OrderItemID AS [OrderItem/OrderItemID],
i.ItemDesc AS [OrderItem/ItemDesc],
oi.Qty AS [OrderItem/Qty],
i.Unitprice * oi.QTy AS [OrderItem/ItemPrice],
(SELECT SubItemID AS [SubItemID],
SubItemDesc AS [SubItemDesc]
FROM @SubItems si
WHERE si.ItemID = i.ItemID
FOR XML PATH(‘SubItem’),TYPE) AS [OrderItem/*]
from @order o
join @Customers c
on c.CustomerID = o.CustomerID
join @OrderItems oi
ON oi.OrderID = o.OrderID
join @Items i
on i.ItemID = oi.ItemID
for xml path(‘Order’),ROOT(‘Orders’)
The output will be as below
If you check the solution it makes use of join to get the related OrderItem details for Orders.
The aliases specified will make sure the OrderItem attributes get organized under a OrderItem node. The subitems for each item are added inside using an inner query and alias is given as OrderItem/* to make sure the SubItem details xml is inserted inside OrderItem node in a nested way.
I find FOR XML PATH construct very handy in situations like above to generate XML in the format we want. Hopefully you’ll also find it interesting in many of your scenarios.
Let me know if you need any more information on this or want to share any experiences you have had with this.
