How to Convert XML Data to SQL Database Table

Introduction: In this article I am going to explain how to select or convert xml data to sql table format.

In previous articles i explained Auto generate auto incremented alphanumeric sequential code in sql server and Get difference in days, hours, minutes and seconds between two dates in sql server and Update table data using inner join in sql server and Query to find all foreign keys referring to particular table and CTE recursive query to get employee manager hierarchy with level in sql 

Implementation: Let’s create two examples to understand how easily we can convert xml data to table format.

Example 1: Suppose we have xml data in following format

DECLARE @xml XML= '<ROOT>

<EMPLOYEE Id="1" Name="Tarun" Age="25" Gender="Male"/>

<EMPLOYEE Id="2" Name="Ritu" Age="27" Gender="Female"/>

<EMPLOYEE Id="3" Name="Arun" Age="24" Gender="Male"/>

<EMPLOYEE Id="4" Name="Jatin" Age="22" Gender="Male"/>

<EMPLOYEE Id="5" Name="Rohan" Age="29" Gender="Male"/>

<EMPLOYEE Id="6" Name="Pooja" Age="20" Gender="Female"/>

</ROOT>'

And we want to select or convert it into table format.

Then the query will be as:

SELECT col.value('@Id','INT') AS EmployeeId, col.value('@Name','VARCHAR(100)') AS EmployeeName,

col.value('@Age','INT') AS Age, col.value('@Gender','VARCHAR(10)') AS Gender

FROM @xml.nodes('/ROOT/EMPLOYEE') tab(col)

Output:

EmployeeId	EmployeeName	Age	Gender
1	Tarun	25	Male
2	Ritu	27	Female
3	Arun	24	Male
4	Jatin	22	Male
5	Rohan	29	Male
6	Pooja	20	Female

Example 2: Suppose we have xml data in following format

DECLARE @xml XML='<ROOT>

<EmployeeId>2</EmployeeId>

<EmployeeId>6</EmployeeId>

<EmployeeId>8</EmployeeId>

</ROOT>'

And we want to select or convert it into table format.

Then the query will be as:

SELECT col.value('.','INT') AS EmployeeId FROM @xml.nodes('/ROOT/EmployeeId')tab(col)

Output:

EmployeeId

2

6

8

 Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 

• Tweet
• Share
• Share
• Share

Related Post