Data Parsing SQL to JSON

Abhishek Jaiswall

4.98/5 (17 votes)

5 Sep 2014CPOL4 min read

120.9K

Data parsing SQL to JSON

Introduction

In this article, I am going to discuss how to convert one type of data into another, then again repeat the procedure for converting that type of data into another form. Basically, data conversion or I better say data parsing sometimes takes all the focus of our project, so this article defines it all how to and why.

Overview
Introduction
Requirements
Procedure

Step: 1
Step: 2
Step: 3

Summary

Overview

As I mentioned above, this article is all about data conversion, so I’ll discuss this in the whole article. I’ll take some demo snippets to show you how it actually works and how easily you can do that using your SQL Server and simple SQL queries. I’ll also demonstrate requirement if necessary further in this article. So just to summarize, I am showing a simple diagram to give you a feel of what this whole article is all about.

Here’s the summary diagram:

As this diagram is saying it all, first of all, I am going to take SQL Server tabular data. Then, I’ll convert into some XML data and further I’ll take this XML data to convert it into JSON format (comma separated).

Introduction

So, now one of the most important questions as to why we need this procedure and what is the requirement of converting simple SQL server data into XML and JSON format?

The answer to this question is as simple as the procedure of converting data itself. Thus, I am going to mention few important knowhows and requirements of doing this procedure over our structures SQL server data.

Requirements

In data visualization procedure, we need simple structured data for further operations, thus we require data in simple XML, JSON, CSV, TSV or some other formats
In creating charts, diagrams, data diagrams
In data manipulation
Storing data in simple structured format
For tool required data, as some tools accept data in some pre defined format for further operations
Data mining
Data warehousing

Procedure

I divided the whole procedure into two separate steps for data conversion. These two steps are as follows:

Tabular data
Tabular data into XML data
XML data into JSON data

Step: 1 | Tabular Data

First of all, we need some data in our table, so for that, I am creating a reference table named as <Shopping>.

Reference Table

SQL

-- Creating a table

CREATE TABLE Shopping
(
Name NVARCHAR(50) not null,
TDate DATETIME not null,
Card_N0 INT not null,
Country NVARCHAR(50) not null,
Gender VARCHAR(10) not null,
Age INT not null,
TYear INT not null,
TMonth INT not null,
);

Inserting Data

SQL

-- Inserting Values

INSERT INTO Shopping
VALUES('Doremon', '2014-02-15', 987, 'USA', 'M', 32, 2014, 02);
INSERT INTO Shopping
VALUES('Dora', '2014-02-05', 123, 'FRA', 'F', 26, 2014, 02);
INSERT INTO Shopping
VALUES('Popeye', '2014-05-11', 487, 'IND', 'M', 32, 2014, 05);
INSERT INTO Shopping
VALUES('Minnie', '2014-06-27', 436, 'UK', 'F', 25, 2014, 06);
INSERT INTO Shopping
VALUES('July', '2014-09-16', 156, 'PR', 'F', 25, 2014, 09);
INSERT INTO Shopping
VALUES('Donald', '2014-12-19', 907, 'JP', 'M', 32, 2014, 12);
INSERT INTO Shopping
VALUES('Goofy', '2014-12-11', 023, 'AUS', 'M', 26, 2014, 12);

Here’s our demo table:

[Tabular Data]

Step: 2 | Tabular data into XML

Now am going to convert our tabular data into XML format, for that you need to write this simple query

SQL

-- Generating XML Data

 SELECT *
   FROM Shopping  
   FOR XML path, root;

This simple SQL query is going to generate XML data for you. When you execute this query, you will get output which is something like:

[XML Data]

In this output window, simply click on <root>.

This will redirect you to a next window where XML data will be waiting for you.

XML data will be something like:

XML

<root>
  <row>
    <Name>Doremon</Name>
    <TDate>2014-02-15T00:00:00</TDate>
    <Card_N0>987</Card_N0>
    <Country>USA</Country>
    <Gender>M</Gender>
    <Age>32</Age>
    <TYear>2014</TYear>
    <TMonth>2</TMonth>
  </row>
  <row>
    <Name>Dora</Name>
    <TDate>2014-02-05T00:00:00</TDate>
    <Card_N0>123</Card_N0>
    <Country>FRA</Country>
    <Gender>F</Gender>
    <Age>26</Age>
    <TYear>2014</TYear>
    <TMonth>2</TMonth>
  </row>
  <row>
    <Name>Popeye</Name>
    <TDate>2014-05-11T00:00:00</TDate>
    <Card_N0>487</Card_N0>
    <Country>IND</Country>
    <Gender>M</Gender>
    <Age>32</Age>
    <TYear>2014</TYear>
    <TMonth>5</TMonth>
  </row>
  <row>
    <Name>Minnie</Name>
    <TDate>2014-06-27T00:00:00</TDate>
    <Card_N0>436</Card_N0>
    <Country>UK</Country>
    <Gender>F</Gender>
    <Age>25</Age>
    <TYear>2014</TYear>
    <TMonth>6</TMonth>
  </row>
  <row>
    <Name>July</Name>
    <TDate>2014-09-16T00:00:00</TDate>
    <Card_N0>156</Card_N0>
    <Country>PR</Country>
    <Gender>F</Gender>
    <Age>25</Age>
    <TYear>2014</TYear>
    <TMonth>9</TMonth>
  </row>
  <row>
    <Name>Donald</Name>
    <TDate>2014-12-19T00:00:00</TDate>
    <Card_N0>907</Card_N0>
    <Country>JP</Country>
    <Gender>M</Gender>
    <Age>32</Age>
    <TYear>2014</TYear>
    <TMonth>12</TMonth>
  </row>
  <row>
    <Name>Goofy</Name>
    <TDate>2014-12-11T00:00:00</TDate>
    <Card_N0>23</Card_N0>
    <Country>AUS</Country>
    <Gender>M</Gender>
    <Age>26</Age>
    <TYear>2014</TYear>
    <TMonth>12</TMonth>
  </row>
</root>

Now we are half done. Now, our next task will convert this XML data into JSON format. So let's move on.

Step: 3 | XML data into JSON data

For converting XML data into JSON, there two sub steps, these steps are:

Declaration & Binding

This is the first sub step, for this step we only need to write a simple query for declaration and bind XML data into it. For that, we use some set of declare and set statement as:

SQL

DECLARE @Shopping xml;
SET @Shopping =
'<?xml version="1.0" encoding="UTF-8"?>
<DATA goes here>’;

Here’s a simple demonstration:

SQL

-- Declaration & Binding

DECLARE @Shopping xml;
SET @Shopping =
'<?xml version="1.0" encoding="UTF-8"?>

<root>
  <row>
    <Name>Doremon</Name>
    <TDate>2014-02-15T00:00:00</TDate>
    <Card_N0>987</Card_N0>
    <Country>USA</Country>
    <Gender>M</Gender>
    <Age>32</Age>
    <TYear>2014</TYear>
    <TMonth>2</TMonth>
  </row>
  <row>
    <Name>Dora</Name>
    <TDate>2014-02-05T00:00:00</TDate>
    <Card_N0>123</Card_N0>
    <Country>FRA</Country>
    <Gender>F</Gender>
    <Age>26</Age>
    <TYear>2014</TYear>
    <TMonth>2</TMonth>
  </row>
  <row>
    <Name>Popeye</Name>
    <TDate>2014-05-11T00:00:00</TDate>
    <Card_N0>487</Card_N0>
    <Country>IND</Country>
    <Gender>M</Gender>
    <Age>32</Age>
    <TYear>2014</TYear>
    <TMonth>5</TMonth>
  </row>
  <row>
    <Name>Minnie</Name>
    <TDate>2014-06-27T00:00:00</TDate>
    <Card_N0>436</Card_N0>
    <Country>UK</Country>
    <Gender>F</Gender>
    <Age>25</Age>
    <TYear>2014</TYear>
    <TMonth>6</TMonth>
  </row>
  <row>
    <Name>July</Name>
    <TDate>2014-09-16T00:00:00</TDate>
    <Card_N0>156</Card_N0>
    <Country>PR</Country>
    <Gender>F</Gender>
    <Age>25</Age>
    <TYear>2014</TYear>
    <TMonth>9</TMonth>
  </row>
  <row>
    <Name>Donald</Name>
    <TDate>2014-12-19T00:00:00</TDate>
    <Card_N0>907</Card_N0>
    <Country>JP</Country>
    <Gender>M</Gender>
    <Age>32</Age>
    <TYear>2014</TYear>
    <TMonth>12</TMonth>
  </row>
  <row>
    <Name>Goofy</Name>
    <TDate>2014-12-11T00:00:00</TDate>
    <Card_N0>23</Card_N0>
    <Country>AUS</Country>
    <Gender>M</Gender>
    <Age>26</Age>
    <TYear>2014</TYear>
    <TMonth>12</TMonth>
  </row>
</root>';

Data Conversion

For data conversion, we are going to use two simple functions both for different functionality, these functions are:

STUFF
(The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position).

COALESCE

(For structured comma separated data)

SQL

-- Function for Conversion | XML to JSON

SELECT Stuff(  
  (SELECT * from  
    (SELECT ',
    {'+  
      Stuff((SELECT ',"'+coalesce(b.c.value('local-name(.)', 'NVARCHAR(MAX)'),'')+'":"'+
                    b.c.value('text()[1]','NVARCHAR(MAX)') +'"'
               
             from x.a.nodes('*') b(c)  
             for xml path(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')
        ,1,1,'')+'}' 
   from @Shopping.nodes('/root/*') x(a)  
   ) JSON(theLine)  
  for xml path(''),TYPE).value('.','NVARCHAR(MAX)' )
,1,1,'')

That operation will give you this in output window:

[JSON Data]

This is nothing but your JSON data. If you copy and paste in other window, it will be something like this:

SQL

      {"Name":"Doremon","TDate":"2014-02-15T00:00:00","Card_N0":"987",_
"Country":"USA","Gender":"M","Age":"32","TYear":"2014","TMonth":"2"}, 
       
          {"Name":"Dora","TDate":"2014-02-05T00:00:00","Card_N0":"123",_
          "Country":"FRA","Gender":"F","Age":"26","TYear":"2014","TMonth":"2"},   
          
             {"Name":"Popeye","TDate":"2014-05-11T00:00:00","Card_N0":"487",_
             "Country":"IND","Gender":"M","Age":"32","TYear":"2014","TMonth":"5"},   
             
                {"Name":"Minnie","TDate":"2014-06-27T00:00:00","Card_N0":"436",_
                "Country":"UK","Gender":"F","Age":"25","TYear":"2014","TMonth":"6"}, 
                 
                    {"Name":"July","TDate":"2014-09-16T00:00:00","Card_N0":"156",_
                    "Country":"PR","Gender":"F","Age":"25","TYear":"2014","TMonth":"9"}, 
                    
                         {"Name":"Donald","TDate":"2014-12-19T00:00:00","Card_N0":"907",_
                         "Country":"JP","Gender":"M","Age":"32","TYear":"2014","TMonth":"12"},  
                          
                            {"Name":"Goofy","TDate":"2014-12-11T00:00:00","Card_N0":"23",_
                            "Country":"AUS","Gender":"M","Age":"26","TYear":"2014","TMonth":"12"}

Congratulations, you are done.

Summary

This was a simple demonstration of data conversion from one to another form. You can do these same operations by writing lines of codes or using JSON serialization. In spite of all these, you can also generate some other type of data too.

I hope you will like it and if you face any problem in this operation, then feel free to ping or message me anytime. I would love to answer your queries.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Data Parsing SQL to JSON

Introduction

Table of Contents

Overview

Introduction

Requirements

Procedure

Step: 1 | Tabular Data

Reference Table

Inserting Data

[Tabular Data]

Step: 2 | Tabular data into XML

[XML Data]

Step: 3 | XML data into JSON data

Declaration & Binding

Data Conversion

[JSON Data]

Summary

License