How To Flatten Xml Data in SQL Server


In this article we will see how we can flatten a xml data and make it more readable. Let us understand the scenario first and then dive into the solution. To understand the scenario we will create a xml file. Load the data in the file into a table in SQL Server.

Step 1:  Create a xml file and store it in the C drive. Below is the content of the file

<?xml version="1.0" ?>
<Root>
<Person>
<Name>John</Name>
<Age>30</Age>
<Hobbies>
<Hobby>PhotoGraphy</Hobby>
<Hobby>Driving</Hobby>
</Hobbies>
</Person>
<Person>
<Name>Wayne</Name>
<Age>21</Age>
<Hobbies>
<Hobby>Fishing</Hobby>
<Hobby>Cooking</Hobby>
</Hobbies>
</Person>
</Root>

Step 2 : Save the file as XmlSource.xml on the C drive. You can choose a different location also.

Step 3: Run the following script and create a table which will store the xml data.

IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'XmlTable')
DROP TABLE XmlTable
go
CREATE TABLE XmlTable
(
XmLId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY ,
XmlData XML NOT NULL
)

Step 4: Run the below script to insert the contents of the xml file into the table

INSERT INTO XmlTable(XmlData)
SELECT * FROM OPENROWSET(BULK 'C:\XmlSource.xml', SINGLE_BLOB)
AS ImportSource
GO

Step 5: Check if the data has been inserted correctly or not.

xml1

 

If we observe we will see that the contents of the xmlData row are in xml format. So how do we make it something like below
xml2

Here you go. The below query will make it happen

SELECT pref.value('(Name/text())[1]', 'varchar(50)') AS Name ,
pref.value('(Age/text())[1]', 'varchar(50)') AS Age,
pref.value('(Hobbies/Hobby/text())[1]', 'varchar(50)') AS Hobby1,
pref.value('(Hobbies/Hobby/text())[2]', 'varchar(50)') AS Hobby2 
FROM
XmlTable
CROSS APPLY
XmlData.nodes('//Person') AS People ( pref )

The quick way to extract the xml data is to use the CROSS APPLY operator which uses a function
against each row and then adds the data to the result set. We then combine this with a function
that can be executed on the XML data type called NODES

 

 


					
Advertisements

Tagged: , , , , , , ,

2 thoughts on “How To Flatten Xml Data in SQL Server

  1. surbhi jain October 26, 2016 at 10:38 am Reply

    Hi, can you please tell why do we flatten an xml file? Thanks

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: