Guru E. Technology

need of every business.


How to Load XML file into SQL Temporary Table

Well, this is very interesting! We can load complete XML file into SQL table and Add/Update records for the table. It was not easy earlier as it seems to be iterating all the nodes picked the value and then insert into table hu! really tough Job.

I will explain you how you can load XML file and create table step by step:

1) Declare XML varaible (ie. DECLARE @XMLFile xml)
2) Set the XML content SET @XMLFile = ‘
3) Declare a table variable (ie. DECLARE @TempTable as TABLE(COL1 VARCHAR(20), COL2 INT);)
4) Add Insert query as SQL:

INSERT INTO @TempTable(COL1,COL2)
SELECT
C.value(‘@FirstCol’, ‘VARCHAR(20)’),
C.value(‘@SecondCol’, ‘int’)
FROM @XMLFile.nodes(‘/ROOT/ParentNode/ChildNode’) as T(C)

SELECT * FROM @TempTable

OUTPUT:

COL1 | COL2
==========================
1st Col Value | 1
1st Col Value1 | 2
1st Col Value2 | 3

This is simple example, I hope this will easly help you to understand how to get the node attributes value as rows from XML file.

Enjoy!

Tags: , , ,

Posted in SQL Server


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>