How to Read or Parse XML Data and Insert into SQL Table

Introduction: In this article I am going to explain how to parse xml to read data and store that data in sql server table, temporary table or table variable using suitable examples.

In previous articles i explained How to Convert xml data to sql database table and Declare and use table variables in sql server and Temporary tables, their types and examples to use

and Query to count male, female and total employees and Difference between primary key and foreign key in sql server

Implementation: Let’s understand how to parse xml to read data and store into sql table using suitable examples.

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 read and insert that data into a table. For this let's create a table using the following script 

CREATE TABLE tbEmployee

(

            EmployeeId                INT,

            EmployeeName         VARCHAR(100),

            Age                            INT,

            Gender                       VARCHAR(10)

)

--Now the query to read data from xml and insert into table will be as:

INSERT INTO tbEmployee (EmployeeId, EmployeeName, Age,Gender)

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

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

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

--Check table data

SELECT * FROM tbEmployee

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

--Similarly we can insert data from xml to temporary table. Create a temporary table using the following script.

CREATE TABLE #tbEmployee

(

            EmployeeId                INT,

            EmployeeName         VARCHAR(100),

            Age                            INT,

            Gender                       VARCHAR(10)

)

--Now the query to read data from xml and insert into temporary table will be as:

INSERT INTO #tbEmployee (EmployeeId, EmployeeName, Age,Gender)

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

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

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

--Check table data. Output will be same as above output

SELECT * FROM #tbEmployee

--We can also insert data from xml to table variable. Create a table variable using the following script.

DECLARE @tbEmployee TABLE

(

            EmployeeId                INT,

            EmployeeName         VARCHAR(100),

            Age                            INT,

            Gender                       VARCHAR(10)

)

--Now the query to read data from xml and insert into table variable will be as:

INSERT INTO @tbEmployee (EmployeeId, EmployeeName, Age,Gender)

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

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

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

--Check table data. Output will be same as above output

SELECT * FROM @tbEmployee  

  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