Generating Nested XML Structures with FOR XML PATH

This blog gives you some insight on how nested XML structures can be easily built using FOR XML PATH construct
Consider the illustration given below

create table parent_child
(
ParentID varchar(50),
ChildID varchar(50)
)

insert parent_child
values ('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.