Click here to Skip to main content
Click here to Skip to main content

Tagged as

Data Parsing SQL to JSON

, 5 Sep 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

Table of Contents

  • 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

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

[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

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

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

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

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

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

Share

About the Author

Abhishek Jaiswall
Software Developer
India India
Hello Buddies!
This is Abhishek, a software scientist and a geek who loves to explore and enjoy new technologies in his own ways. He loves every aspect of technology.
Follow on   Twitter   LinkedIn

Comments and Discussions

 
QuestionHmmmmmmm Pinmemberlespauled8-Sep-14 7:03 
AnswerRe: Hmmmmmmm PinmemberJohn B Oliver1-Oct-14 12:21 
SuggestionA different approach PinmemberPeejayAdams8-Sep-14 2:42 
GeneralRe: A different approach PinmemberAbhishek Jaiswall9-Sep-14 6:06 
GeneralRe: A different approach PinmemberPeejayAdams9-Sep-14 6:12 
QuestionOnly 1% of the solution PinmemberNeverJustHere7-Sep-14 2:53 
QuestionOne suggestion PinmemberTridip Bhattacharjee6-Sep-14 9:22 
AnswerRe: One suggestion PinmemberAbhishek Jaiswall6-Sep-14 22:56 
GeneralMy vote of 3 PinmemberTridip Bhattacharjee6-Sep-14 9:21 
GeneralRe: My vote of 3 PinmemberAbhishek Jaiswall6-Sep-14 22:53 
GeneralMy vote of 5 PinmemberCarsten V2.05-Sep-14 5:40 
GeneralRe: My vote of 5 PinmemberAbhishek Jaiswall5-Sep-14 7:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.141022.2 | Last Updated 5 Sep 2014
Article Copyright 2014 by Abhishek Jaiswall
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid