Example SQL Server TSQL Stored Procedure to produce JSON

With no native support for JSON in MS SQL Server 2012, hacks and work-arounds abound.

One of the best I’ve come across is a tidy little stored procedure from from Jamin Quimby.

It works like a treat and seems to produce JSON that validates cleanly every time.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SerializeJSON] (@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

It takes any SQL query as a parameter.

EXEC [dbo].[SerializeJSON] "SELECT * FROM myTable;";

When you setup this stored procedure you’ll need to grant execute permissions. I have a small team of seasoned high performers (lucky me!), so I can afford to be more liberal with permissions. In which case I’ll grant execute permission to the SQL Server public role.


GRANT EXECUTE ON dbo.SerializeJSON  TO public

Now you could schedule this as a SQL Agent Job to dump your JSON file regularly to a wwwroot directory but in a shared development environment this is cumbersome. Especially if coders need perms to modify (or copy) the Agent Job on the SQL Server. Or if you’re using SQLExpress, you don’t even have Agents Jobs.

An alternative is to create a PowerShell script with promiscuous permissions.

Import-Module 'sqlps' -DisableNameChecking;

Invoke-Sqlcmd `
 -ServerInstance "myHostName\myInstanceName" `
 -Database "myDatabaseName" `
 -Query "EXEC [dbo].[SerializeJSON] `"SELECT TOP 100 * FROM myTableName`";";

Set permissions with this command in the PowerShell Console,

Set-ExecutionPolicy Unrestricted

Now this little .ps1 script can be copied, shared and edited with a text editor. You could use Windows Task Scheduler to run this periodically to refresh your test data.

Simple. Effective.

Advertisements

21 thoughts on “Example SQL Server TSQL Stored Procedure to produce JSON

  1. One cannot get around using JSON and Javascript Object Literal in applications today. By delivering Objects directly saves application server overhead and increases response times. For me I use this mostly to load large data sets in a Object Literal format then push it to a HTML5 storage location directly though AJAX listeners. The end user experience is seamless when scrolling of large data sets. Since my customary response to this post would appear oddly self-serving, I shall simply say; Good Luck.

  2. This is exactly what i am looking for! But i am getting an error by the charIndex at line 57. Am i dooing something wrong?

    /Kim

  3. I have to change my SQL as:
    [SerializeJSON] ‘select case when isnull(email,”NOEMAIL”) = ”” then ”NOEMAIL” else email end AS Email,username,password from Users ‘

    • Tengo el mismo problema que el compañero en esta linea, que me falta
      SET @SQL = @SQL + @ParameterSQL + ‘FOR XML RAW,TYPE,ELEMENTS’

    • Nice query, line 18 needs a space before the FOR XML RAW. This is from Brian O’Malley. I did the PRINT statement, it needed a “SPACE”.

  4. with @ParameterSQL = N’test’
    I get
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘testFOR’.

    with
    @ParameterSQL = N'[test]’
    I get
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘test’.

  5. Update:
    This is when I right click and execute SP in SQL Manager.
    However, if i run “EXEC [dbo].[SerializeJSON] “SELECT * FROM [items]”;”
    It works.
    Thanks

  6. Anyone know how to leverage this to create nested elements? So if I need my output from my query to be:

    { “name” : “val”,
    “something” : “val”.
    “nested element” : { “nestedname” : “val”,
    “something else” : “val”}
    }

  7. Thank you very much for posting. It is really useful.
    What I get is an error when the sql sentence (@parameter) is longer than 128 chars. Is there a way to use a longer sql sentence?
    Thanks again

  8. Hi,
    very helpful, thank you!
    I added this between line 67 and 68 to handle empty values:

    IF SubString(@Row, @FieldEnd-1, 1) = ‘/’ BEGIN — empty tag
    SET @KEY = SubString(@Row, @FieldStart, @FieldEnd – @FieldStart – 1)
    SET @JSON = @JSON + ‘”‘ + @KEY + ‘”:””,’
    SET @FieldStart = CharIndex(@StartField, @Row, @FieldEnd)
    END ELSE BEGIN

    And END between line 77 and 78

  9. Hi,
    very, very helpful indeed.
    If you wanted all the columns including NULL values columns, I added the following code.
    Replace line 18 with this line
    SET @SQL = @SQL + @ParameterSQL + ‘ FOR XML RAW,TYPE, Elements XSINIL’
    Also, after line 26 add these two lines of code:
    set @XMLString = Replace(@XMLString, ‘ xsi:nil=”true”‘, ”)
    set @XMLString = Replace(@XMLString, ‘ xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”‘, ”)
    (you may want to verify the XML contains ‘xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance’ otherwise, just modify it to suite your needs.
    I hope this helps you! and thanks again! cheers

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s