Generate JSON format from table and export as JSON File from MSSQL

I have gone through many post constructing JSON Strings in SQL Server, most of them are not working as we expect. Finally I found the following code, which gives perfect output and more convenient to customize.

CREATE PROCEDURE [dbo].[GetJSON] (

@ParameterSQL AS VARCHAR(MAX)

)

AS

BEGIN

DECLARE @SQL NVARCHAR(MAX)

DECLARE @XMLString VARCHAR(MAX)

DECLARE @XML XML

DECLARE @Paramlist NVARCHAR(1000)

SET @Paramlist = N'@XML XML OUTPUT'

SET @SQL = 'WITH PrepareTable (XMLString) '

SET @SQL = @SQL + 'AS ( '

SET @SQL = @SQL + @ParameterSQL+ ' FOR XML RAW, TYPE, ELEMENTS '

SET @SQL = @SQL + ') '

SET @SQL = @SQL + 'SELECT @XML = XMLString FROM PrepareTable '

EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT

SET @XMLString = CAST(@XML AS VARCHAR(MAX))

DECLARE @JSON VARCHAR(MAX)

DECLARE @Row VARCHAR(MAX)

DECLARE @RowStart INT

DECLARE @RowEnd INT

DECLARE @FieldStart INT

DECLARE @FieldEnd INT

DECLARE @KEY VARCHAR(MAX)

DECLARE @Value VARCHAR(MAX)

DECLARE @StartRoot VARCHAR(100); SET @StartRoot = '<row>'

DECLARE @EndRoot VARCHAR(100); SET @EndRoot = '</row>'

DECLARE @StartField VARCHAR(100); SET @StartField = '<'

DECLARE @EndField VARCHAR(100); SET @EndField = '>'

SET @RowStart = CharIndex(@StartRoot, @XMLString, 0)

SET @JSON = ''

WHILE @RowStart > 0

BEGIN

      SET @RowStart = @RowStart+Len(@StartRoot)

      SET @RowEnd = CharIndex(@EndRoot, @XMLString, @RowStart)

      SET @Row = SubString(@XMLString, @RowStart, @RowEnd-@RowStart)

      SET @JSON = @JSON+'{'

      -- for each row

      SET @FieldStart = CharIndex(@StartField, @Row, 0)

      WHILE @FieldStart > 0

      BEGIN

            -- parse node key

            SET @FieldStart = @FieldStart+Len(@StartField)

            SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)

            SET @KEY = SubString(@Row, @FieldStart, @FieldEnd-@FieldStart)

            SET @JSON = @JSON+'"'+@KEY+'":'

            -- parse node value

            SET @FieldStart = @FieldEnd+1

            SET @FieldEnd = CharIndex('</', @Row, @FieldStart)

            SET @Value = SubString(@Row, @FieldStart, @FieldEnd-@FieldStart)

            SET @JSON = @JSON+'"'+@Value+'",'

            SET @FieldStart = @FieldStart+Len(@StartField)

            SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)

            SET @FieldStart = CharIndex(@StartField, @Row, @FieldEnd)

      END  

      IF LEN(@JSON)>0 SET @JSON = SubString(@JSON, 0, LEN(@JSON))

      SET @JSON = @JSON+'},'

      --/ for each row

      SET @RowStart = CharIndex(@StartRoot, @XMLString, @RowEnd)

END

IF LEN(@JSON)>0 SET @JSON = SubString(@JSON, 0, LEN(@JSON))

SET @JSON = '[' + @JSON + ']'

SELECT @JSON

END

To run:

EXEC GetJSON 'SELECT * FROM Production.ProductCategory'

The output will be as below

[

{"ProductCategoryID":"1","Name":"Bikes","rowguid":"CFBDA25C-DF71-47A7-B81B-64EE161AA37C","ModifiedDate":"2002-06-01T00:00:00"},

{"ProductCategoryID":"2","Name":"Components","rowguid":"C657828D-D808-4ABA-91A3-AF2CE02300E9","ModifiedDate":"2002-06-01T00:00:00"},

{"ProductCategoryID":"3","Name":"Clothing","rowguid":"10A7C342-CA82-48D4-8A38-46A2EB089B74","ModifiedDate":"2002-06-01T00:00:00"},

{"ProductCategoryID":"4","Name":"Accessories","rowguid":"2BE3BE36-D9A2-4EEE-B593-ED895D97C2A6","ModifiedDate":"2002-06-01T00:00:00"}

]

If the Select query is complex, then insert the query result to temp table, and then pass the temp table query to the JSON stored procedure.

For Example: EXEC GetJSON 'SELECT * FROM #Result'

You can export the output as JSON file using following command:

Modify the SP with output parameter:

CREATE PROCEDURE [dbo].[GetJSON] (

@ParameterSQL AS VARCHAR(MAX),

@JsonString AS VARCHAR(MAX) OUTPUT

)

AS

BEGIN

DECLARE @SQL NVARCHAR(MAX)

DECLARE @XMLString VARCHAR(MAX)

DECLARE @XML XML

DECLARE @Paramlist NVARCHAR(1000)

SET @Paramlist = N'@XML XML OUTPUT'

SET @SQL = 'WITH PrepareTable (XMLString) '

SET @SQL = @SQL + 'AS ( '

SET @SQL = @SQL + @ParameterSQL+ ' FOR XML RAW, TYPE, ELEMENTS '

SET @SQL = @SQL + ') '

SET @SQL = @SQL + 'SELECT @XML = XMLString FROM PrepareTable '

EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT

SET @XMLString = CAST(@XML AS VARCHAR(MAX))

DECLARE @JSON VARCHAR(MAX)

DECLARE @Row VARCHAR(MAX)

DECLARE @RowStart INT

DECLARE @RowEnd INT

DECLARE @FieldStart INT

DECLARE @FieldEnd INT

DECLARE @KEY VARCHAR(MAX)

DECLARE @Value VARCHAR(MAX)

DECLARE @StartRoot VARCHAR(100); SET @StartRoot = '<row>'

DECLARE @EndRoot VARCHAR(100); SET @EndRoot = '</row>'

DECLARE @StartField VARCHAR(100); SET @StartField = '<'

DECLARE @EndField VARCHAR(100); SET @EndField = '>'

SET @RowStart = CharIndex(@StartRoot, @XMLString, 0)

SET @JSON = ''

WHILE @RowStart > 0

BEGIN

      SET @RowStart = @RowStart+Len(@StartRoot)

      SET @RowEnd = CharIndex(@EndRoot, @XMLString, @RowStart)

      SET @Row = SubString(@XMLString, @RowStart, @RowEnd-@RowStart)

      SET @JSON = @JSON+'{'

      -- for each row

      SET @FieldStart = CharIndex(@StartField, @Row, 0)

      WHILE @FieldStart > 0

      BEGIN

            -- parse node key

            SET @FieldStart = @FieldStart+Len(@StartField)

            SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)

            SET @KEY = SubString(@Row, @FieldStart, @FieldEnd-@FieldStart)

            SET @JSON = @JSON+'"'+@KEY+'":'

            -- parse node value

            SET @FieldStart = @FieldEnd+1

            SET @FieldEnd = CharIndex('</', @Row, @FieldStart)

            SET @Value = SubString(@Row, @FieldStart, @FieldEnd-@FieldStart)

            SET @JSON = @JSON+'"'+@Value+'",'

            SET @FieldStart = @FieldStart+Len(@StartField)

            SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)

            SET @FieldStart = CharIndex(@StartField, @Row, @FieldEnd)

      END  

      IF LEN(@JSON)>0 SET @JSON = SubString(@JSON, 0, LEN(@JSON))

      SET @JSON = @JSON+'},'

      --/ for each row

      SET @RowStart = CharIndex(@StartRoot, @XMLString, @RowEnd)

END

IF LEN(@JSON)>0 SET @JSON = SubString(@JSON, 0, LEN(@JSON))

SET @JSON = '[' + @JSON + ']'

SET @JsonString = @JSON

END

To export as file

DECLARE @Jsonoutput VARCHAR(MAX)

DECLARE @Xpcommand VARCHAR(8000)

EXEC dbo.GetJSON 'SELECT * FROM # Result', @Jsonoutput OUT

SET @Xpcommand = 'echo ' + @Jsonoutput + ' >C:\output\outputfile.json'

EXEC master..xp_cmdshell @Xpcommand

Cheers!
Uma