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
- 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
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
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
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:
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)
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.
Geek | Blogger | Data Science Scholar | TechSavvy | Developer | Painter | Author | Trainer | Tech Evangelist | Philanthropist
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
