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.