Please disable your adblock and script blockers to view this page
 

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.


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. 
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. ConversionConversion EmoticonEmoticon