T-SQL Recipie: Generate XML/JSON Output

Change of Circumstances

Life is about change, I had never thought one day I would work in a Java/HP Vertica powered cloud BI environment. But it is the fact. How amazing it is...

So I am going to add a few things different to MS techs in the future, some techs related to my current job. So if you see something different to my previous post, don't worry, it is still me :P

Now back to the topic, one of my previous colleagues dropped a question to me today. Basically the core of the question is how to reflect parent/child relationship when exporting query result into XML/JSON. It is quite a common scenario today, so have a quick look at below code:

use tempdb
go

declare @cust table (custid int identity(1,1), custname varchar(50))
declare @sale table (saleid int identity(1, 1), custid int, amount money)

insert into @cust(custname) values ('CQI'), ('ABC'), ('JSMITH')
insert into @sale(custid, amount) values (1, 100), (1, 20), (2, 55), (2, 80), (2, 16), (3, 10)

select * from @cust c join @sale s on c.custid = s.custid


--for xml
--valid since sql2008
--use corelate query to feed child records
--TYPE is a must to ensure return type is xml
select c.custid as '@custid',
 c.custname as '@custname',
 (select saleid as '@saleid', amount as '@amount' from @sale where custid = c.custid for xml path('sales'), TYPE)
from @cust c
for xml path('cust'), root('customersales')

--for json
--2016 only
select c.custid as '@custid',
 c.custname as '@custname',
 (select saleid as '@saleid', amount as '@amount' from @sale where custid = c.custid for json path) as Sales
from @cust c
for json path

So if we run the code listed above:

And if we expand the XML result

 

And here is the JSON result

Hope it can give you some ideas if you have a need to transfer the result set into XML/JSON format. But please keep in mind:

• For JSON clause is for SQL Server 2016 only
• In XML output the TYPE keyword in co-related query is required, to ensure output is XML type.

So that is it. Enjoy.