Written by Irmak Tevfik on 25 - Oct - 2014

SQL Server function to convert XML to JSON format

This weekend I decided to test the performance of searching Azure Table Storage with saved keys on Redis Cache versus without any caching (direct search). Idea came when I was watching Azure Friday on Channel9 this week as Scott Hanselman and Saurabh Pant​ was pointing out Application Patterns on Redis Cache. 
So as a first step I decided to get "Realistic" dummy data to be generated. Usually I hold some random generated data on my local database for testing purposes. But this this time thought it will be better to code a simple Windows Forms application to generate random data (as long as we have some sample JSON data). Then decided to have my dummy table data to be parsed as in JSON format. If anyone interested, you can easily convert by using the following function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 25/10/2014
-- Description: www.4sln.com
-- =============================================
CREATE FUNCTION dbo.fn_XmlToJson_Get
(
@XmlData XML
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN
 (SELECT STUFF( 
  (SELECT
   *
   FROM 
    (SELECT
      ',{'
        STUFF(
          (SELECT
            ',"'+
             COALESCE(b.c.value('local-name(.)', 'NVARCHAR(MAX)'),'')+'":"'+ b.c.value('text()[1]','NVARCHAR(MAX)') +'"'
           FROM x.a.nodes('*') b(c) FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'')
       +'}'
     FROM @XmlData.nodes('/root/*') x(a)) JSON(theLine) 
    FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)' )
   ,1,1,''))
END
GO

It is pretty simple actually. Conversion will take place from XML to JSON. But bear in mind that it might take some time for large data amount. So to run, simply try it as:

SELECT (SELECT TOP 10 *
   FROM dbo.[DummyData] 
   FOR XML path, root) AS MyRow into #t1
 
select dbo.fn_XmlToJson_Get(MyRow) from #t1

In this case, [DummyData] ​is my table where I hold my test data. I will convert it to XML on rowname Myrow and push the data into a temp table. Once I run the query I will be getting:

{"Gender":"male","Title":"Mr.","Surname":"Patrick","GivenName":"David","TelephoneNumber":"281-480-8202"},
{"Gender":"female","Title":"Mrs.","Surname":"Bradley","GivenName":"Joan","TelephoneNumber":"914-479-3345"},
{"Gender":"male","Title":"Mr.","Surname":"Derrickson","GivenName":"Michel","TelephoneNumber":"407-862-6171"},
{"Gender":"male","Title":"Mr.","Surname":"Brockett","GivenName":"Timothy","TelephoneNumber":"559-528-7971"},
{"Gender":"male","Title":"Mr.","Surname":"Goldsmith","GivenName":"Alan","TelephoneNumber":"978-740-2316"},
{"Gender":"male","Title":"Mr.","Surname":"Jackson","GivenName":"Bruce","TelephoneNumber":"808-955-0386"},
{"Gender":"male","Title":"Mr.","Surname":"Weston","GivenName":"James","TelephoneNumber":"856-286-2674"},
{"Gender":"male","Title":"Mr.","Surname":"Jackson","GivenName":"Walter","TelephoneNumber":"616-551-5857"},
{"Gender":"male","Title":"Mr.","Surname":"Farias","GivenName":"John","TelephoneNumber":"217-899-1424"},
{"Gender":"female","Title":"Ms.","Surname":"Shelton","GivenName":"Samantha","TelephoneNumber":"720-566-8669"}

Simple as this. I will move to the article where we will create our own dummy data generator. 

Enjoy Coding :) comments powered by Disqus