T-SQL Tips: Fun with FOR XML PATH

Quite often in T-SQL the first thing that comes to our mind when we hear about FOR XML PATH feature is the ability to concatenate row values using it to create a delimited list of values. I myself had blogged o it here
http://visakhm.blogspot.in/2014/01/rowset-concatenation-with-special.html
Apart from that very few realize the fact that FOR XML PATH provides easier method to generate flexible XML structures from relational data stored in SQL tables. This post is intended to give some insight into the usage of FOR XML PATH feature for generating flexible XML structures.
Consider the below table for the illustration.

Now lets see how we can apply FOR XML PATH feature to build flexible xml structures from the above relational data.
Lets start with the simplest one. A simple query as below

select * from Orders 
for xml path('Order'),Root('Orders')

will give you the below result

Now we can slightly tweak this to get values as attribute values as below

select OrderID AS [@OrderID],
CustName AS [@CustName],
OrderDate AS [@OrderDate],
ReferredBy AS [@ReferredBy]
from Orders 
for xml path('Order'),Root('Orders')

Going one step further we can also generate nested XML structures. For example in the above table we have multiple orders coming for the same customer. We can group them by customer and show the details as nested XML using a query like below

select CustName,

(SELECT OrderID,OrderDate,ReferredBy FROM Orders WHERE CustName = t.CustName FOR XML PATH('Order'),TYPE) AS [Orders]

from (SELECT DISTINCT CustName  FROM Orders)t

for xml path('Customer'),ROOT('Customers')

The result would be as below

Similarly you can group on any of common field values and nest xml based on that. The grouping based on Agent can be obtained using query as below

select ReferredBy AS [@AgentName],

(SELECT OrderID,CustName,OrderDate FROM Orders WHERE ReferredBy = t.ReferredBy FOR XML PATH('Order'),TYPE) AS [Orders]

from (SELECT DISTINCT ReferredBy  FROM Orders)t

for xml path('Agent'),ROOT('Agents')

And the output obtained would be this

EDIT: 

Added one more scenario to illustrate multiple level nesting. The table above is extended to include AgentGroup information. The modified script is as below

https://drive.google.com/file/d/0B4ZDNhljf8tQVkNNbm1SMmtBOWs/edit?usp=sharing

then use a query like below

select
AgentGrp AS [@AgentGroup],
(
select ReferredBy AS [@AgentName],
(SELECT OrderID,CustName,OrderDate FROM Orders WHERE ReferredBy = t.ReferredBy FOR XML PATH('Order'),TYPE) AS [Orders]
from (SELECT DISTINCT ReferredBy  FROM Orders WHERE AgentGrp = r.AgentGrp)t
for xml path('Agent'),type) AS [*]
FROM (SELECT DISTINCT AgentGrp  FROM Orders )r
for xml path('AgentGroup'),ROOT('AgentGroups')

And you can see output as below with first grouping at AgentGroups and then at Agents and Order information coming within it

As you see from the above examples we can tweak the query using FOR XML PATH to generate flexible xml structures based on our requirement. 

This method can be leveraged upon to get data in flexible XML formats and comes very handy in cases where we want to pass large amount of data between modules as resultsets.

Hope you will find this useful. I will be explaining more scenarios in a follow up blog.