Data Parsing SQL to JSON

Hello geeks, this article explains 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.

Outlines

• Overview
• Introduction
• Requirements
• Procedure
• Summary

Overview

As I said above this article is all about data conversion, so I'll be explaining that in the entire 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 the requirement if necessary further in this article. So just brush up the entire article summary. I am showing a simple diagram to provide you a feel of what this entire article is all about.

Here's a summarized diagram:

Since this diagram says it all, first of all will use SQL Server tabular data then I'll convert into some XML data and further I'll use this XML data to convert it into JSON format (comma separated).

Introduction

So, now one of the most important questions is why we need this procedure and what the requirement is for converting simple SQL Server data into XML and JSON format.
The answer to this question is as simple as the procedure of converting the data itself. Thus, we will mention a few important facts and requirements for doing this procedure over our structured SQL Server data.

Requirements

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

Procedure

I divided the entire 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 <Shopping>.

Reference Table

-- 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

-- 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:



Step 2: Tabular data into XML

Now will convert our tabular data into XML format, for that you need to write the following simple query:

-- Generating XML Data
SELECT * FROM Shopping
FOR XML path, root;

This simple SQL query will generate XML data for you. When you execute this query you will get output something like:

In this output window simply click on <root>.

 

This will redirect you to another window where XML data will be waiting for you.
The XML data will be something like:

<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 is to converitn this XML data into JSON format. So let's go.

Step 3: XML data into JSON data

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

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

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

  Here's a simple demonstration:
  

  -- 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 will 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:

  -- 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 the output window:
  
  
  
  This is nothing but your JSON data. If you copy and paste it in another window, it will be something like this:
  

  {"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"}

Now, it is 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 experience any problem in this operation then feel free to ping or message me anytime. I would love to answer your queries.