You are currently viewing How to convert XML to JSON in MS SQL SERVER

JSON is a data-interchange format that is both Human and Machine readable. It is easy for developers to interpret and pinpoint the issues, if any, during debugging when working with JSON. If the data is sent from our database to the application in JSON format. It will definitely make developers life easy in many ways.

This can be easily done in SQL 2016 by specifying the clause ‘for JSON auto‘ at the end of our return statement.

But, for developers and organisations that are using earlier versions of ms sql server, it’s a challenging ask. though this can be achieved simply by doing the following.

 

To Convert our query output to JSON, we need to convert the query output to XML first!

This can be done by specifying the ‘for XML auto‘ or more specific ‘for xml raw, elements, type‘ clause at the end of return statement and then sending the xml to this t-sql function as a parameter.

CREATE FUNCTION [dbo].[FnJsonEscape](@val nvarchar(max) )

returns nvarchar(max)

as begin

if (@val is null) return 'null'
if (TRY_PARSE( @val as float) is not null) return @val
set @val=replace(@val,'\','\\')
set @val=replace(@val,'"','\"')
return '"'+@val+'"'
end
GO

CREATE FUNCTION [dbo].[FnXmlToJson](@Xml XML)

RETURNS NVARCHAR(MAX)

AS
BEGIN
DECLARE @json NVARCHAR(MAX);
SELECT @json = STUFF(
(
SELECT JSONValue
FROM
(
SELECT ','+' {'+STUFF(
(
SELECT ',"'+COALESCE(b.c.value('local-name(.)', 'NVARCHAR(max)'), '')+'":'+CASE
WHEN b.c.value('count(*)', 'int') = 0
THEN dbo.[FnJsonEscape](b.c.value('text()[1]', 'NVARCHAR(MAX)'))
ELSE dbo.FnXmlToJson(b.c.query('*'))
END
FROM x.a.nodes('*') b(c)
FOR XML PATH(''), TYPE
).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 1, '')+'}'
FROM @Xml.nodes('/*') x(a)
) JSON(JSONValue)
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
RETURN @json;
END;
GO

CREATE FUNCTION [dbo].[FnXmlToJsonList](@Xml XML)

RETURNS NVARCHAR(MAX)

AS
BEGIN
DECLARE @json NVARCHAR(MAX);
SELECT @json = '[' + STUFF(
(
SELECT JSONValue
FROM
(
SELECT ','+' {'+STUFF(
(
SELECT ',"'+COALESCE(b.c.value('local-name(.)', 'NVARCHAR(max)'), '')+'":'+CASE
WHEN b.c.value('count(*)', 'int') = 0
THEN dbo.[FnJsonEscape](b.c.value('text()[1]', 'NVARCHAR(MAX)'))
ELSE dbo.FnXmlToJson(b.c.query('*'))
END
FROM x.a.nodes('*') b(c)
FOR XML PATH(''), TYPE
).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 1, '')+'}'
FROM @Xml.nodes('/*') x(a)
) JSON(JSONValue)
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')+']';
RETURN @json;
END;
GO

The FnXmlToJsonList/FnXmlToJson scalar valued function will return the variable passed to the function as xml with JSON

The xml can be noded and elemented by specifying the clause ‘FOR XML RAW, TYPE, ELEMENTS‘ as mentioned earlier

Note: Please make sure that the length of data in each of the mentioned column is less than 4000 characters for each cell.

Post comments if this helps

 

Leave a Reply