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
