How to use JSON in SQL Server 2008, 2012 and 2014 - Reading JSON strings, importing to the bank and exporting to XML

Reading Time: 10 minutes

Hello guys,
Good afternoon.

In this post I will demonstrate how to read JSON strings and convert to a table and convert data from a table to JSON and XML using only T-SQL. This tip is very useful when you are using an earlier version of SQL Server 2016 and need to read Json strings.

If you are using SQL Server 2016 or higher, be aware that native JSON support already exists. To learn how this native support works, read my post. SQL Server 2016 - Using Native JSON Support (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY).

What is JSON

JSON, an acronym for "JavaScript Object Notation", is a lightweight format for exchanging computational data. JSON's simplicity has resulted in its widespread use, especially as an alternative to XML in AJAX.

One of the claimed advantages of JSON over XML as a format for data exchange in this context is that it is much easier to write a JSON parser. In JavaScript itself, JSON can be parsed trivially using the eval () function. This was important for JSON acceptance within the AJAX community due to the presence of this JavaScript feature in all current web browsers.

In practice, arguments about the ease of development and performance of the parser are rarely highlighted because of the security concerns of using eval () and the increasing integration of XML processing in modern web browsers. For this reason JSON is typically used in environments where the size of the data flow between client and server is of paramount importance (hence its use by Google, Yahoo, etc., which serve millions of users), where the source of data can be explicitly reliable, and where the loss of client-side XSLT processing resources for data manipulation or interface generation is not a consideration.

A great use for using JSON in SQL Server and what motivated me to make this post is for integrating and consuming data from webservices that use JSON to communicate with the information consumer client.

Reading a JSON string

Using the function that I'm going to post below, created by Phil Factor, you can read JSON strings easily, as in the example below:

Implementing the function:
View source

Creating a JSON string from the bank

Using this function, you can create a JSON string from a table in a specific format. For this we need to create this kind of data before using our function.

Prerequisite - Creating the function to handle the data:

Prerequisite - Creating the JSON type:

Implementing the function:
View source

Creating an XML from a JSON

Using the function fncJSON_To_XML, you can create XML from a JSON string passed to the function, like the example below:

If you need help reading and manipulating data in XML objects using SQL Server, learn how to do this by reading my article. SQL Server - How to read, import, and export data from XML files.

Implementing the function:
View source

Workaround for converting a JSON string to XML

With the function below (written by Daniel Muchmacher), you can easily convert Json strings to an XML document, where you can use SQL Server to navigate between nodes and read the data as needed. This function is very useful for instances that are using an earlier version of SQL Server 2016 (without native JSON support), but you need to use this feature.

If you need help reading and manipulating data in XML objects using SQL Server, learn how to do this by reading my article. SQL Server - How to read, import, and export data from XML files.

Example:

Result:

Function source code

That's it.
Until the next post!