Introduction
This article explains how to convert one type of data into another and repeats the procedure for converting that type of data into another form. Data conversion, or data parsing, sometimes takes all the focus of our project, so this article defines it all, how to and why.
So, now one of the most important questions is why we need this procedure and the requirement for converting simple SQL Server data into XML and JSON format.
The answer to this question is as simple as converting the data itself. Thus, we will mention a few important facts and requirements for doing this procedure over our structured SQL Server data.
Outlines
- Overview
- Introduction
- Requirements
- Procedure
- Summary
Overview
As I said above, this article is all about data conversion, so I'll explain that in the entire article. I'll take some demo snippets to show you how it 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 brush up on the entire article summary. I am showing a simple diagram to give you a feel for this article.
Here's a summarized diagram:
Since this diagram says it all, first of all, I will use SQL Server tabular data, then I'll convert it into some XML data, and further, I'll use this XML data to convert it into JSON format (comma separated).
Requirements
- We need simple structured data for further operations in a data visualization procedure. 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 a simple structured format
- For tool-required data, some tools accept data in a 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, we need some data in our table, so 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,
);
Above SQL statement create new table in SQL database.
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);
Use the above SQL INSERT statement to insert the records in table named shopping.
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, click on <root>.
This will redirect you to another window where XML data will await you.
The XML data will be something like this.
<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 convert 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 sets of declare and set statements.
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:
-- 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 into another window, it will be 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"}
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.
COALESCE
For structured comma-separated data.
Summary
This was a simple demonstration of data conversion from one form to another. You can do these same operations by writing lines of code or using JSON serialization. Despite all these, you can also generate some other types of data.
I hope you will like it, and if you experience any problem in this operation, feel free to ping or message me anytime. I would love to answer your queries.
Shameer KCPosted Oct 22, 2016, 9:21 AM
Excellent Article, the JSON data was not showing empty columns. Can that be displayed.
Abhishek JaiswalPosted Jun 29, 2015, 1:31 PM
Aah, thanks!! :)
Upendra Pratap ShahiPosted Jun 29, 2015, 6:55 AM
nice one Abhishek Jaiswal :)
Abhishek JaiswalPosted Apr 8, 2015, 5:59 AM
thanks buddy! :)
Gowtham RajamanickamPosted Apr 7, 2015, 6:21 AM
good one..
Abhishek JaiswalPosted Apr 3, 2015, 2:30 PM
Thanks buddy! :)
Udaya kumarPosted Apr 2, 2015, 2:18 AM
nice ya...
Abhishek JaiswalPosted Dec 23, 2014, 8:45 AM
i don't think its possible. but in case u find any.. kindly let me know! :)
niklesh soniPosted Dec 23, 2014, 3:49 AM
can we direct convert sql data to json format
Abhishek JaiswalPosted Dec 22, 2014, 3:34 AM
keep encouraging me! :)
Mg KyaingPosted Nov 7, 2014, 11:29 AM
Thank sir.............
Abhishek JaiswalPosted Sep 6, 2014, 10:46 AM
Thanks buddy!
Sandeep VaghelaPosted Sep 6, 2014, 10:43 AM
Really Good.
Abhishek JaiswalPosted Sep 5, 2014, 1:50 PM
Thanks! :)
Ramesh MaruthiPosted Sep 5, 2014, 1:31 PM
good 1