Parse JSON with TSQL in a SQL Server Stored Procedure

json_logo-555px

I’m a big fan of JSON as a data exchange format, it’s lightweight, and every API worth playing with is usually JSON-friendly. So in idle curiosity I wondered if it was possible to parse JSON with TSQL, and guess what, someone else has come up with a way to do so.

A hat tip to Phil Factor (and RedGate for their great Simple Talk blog) for his article Consuming JSON Strings in SQL Server. Phil not only has example code to parse JSON into a table format, he shows you how to go the other way – create JSON from table output.

SQL Server does handle XML, but let’s be honest, it’s a pain in rear to use. But it’s all we’ve got natively with SQL, as Phil says:

TSQL isn’t really designed for doing complex string parsing, particularly where strings represent nested data structures such as XML, JSON, YAML, or XHTML.

You can do it but it is not a pretty sight; but why would you ever want to do it anyway? Surely, if anything was meant for the ‘application layer’ in C# or VB.net, then this is it. ‘Oh yes’, will chime in the application thought police, ‘this is far better done in the application or with a CLR.’ Not necessarily.

Sometimes, you just need to do something inappropriate in TSQL.

I gave the code a quick try parsing JSON into a table and it worked great.

 

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