Read XML Data as a table in SQL Server 1


Sometimes we need bulk data to be sent to SQL Server in procedure parameter and inside procedure we perform some insert or update or delete or even another select with joins etc. And in such type of scenarios, we prefer to send these bulk data in XML format to SQL Server. So in this post we will learn, how we can read xml data in a table. Proceeding with “Read XML data as a table”,  first of all we will create a demo XML variable to perform the select operation on it.

Read XML Data

XML Data

Now we have to read this data in a table to be used further, To get that first have a look on the below select statement;


SELECT
tbl.col.value('ID[1]', 'smallint'),
Tbl.Col.value('Name[1]', 'varchar(100)'),
Tbl.Col.value('Age[1]', 'smallint'),
Tbl.Col.value('Gender[1]', 'varchar(10)'),
Tbl.Col.value('City[1]', 'varchar(50)'),
Tbl.Col.value('State[1]', 'varchar(50)')

FROM   @xml.nodes('/DataTable/Employee') tbl(col)

This select statement will output as below;

Read XML Data

XML as Table

Now we can put this data inside any temporary or permanent staging area of SQL Server.

Add Select * Into style inside the above code as like below;


SELECT
tbl.col.value('ID[1]', 'smallint'),
Tbl.Col.value('Name[1]', 'varchar(100)'),
Tbl.Col.value('Age[1]', 'smallint'),
Tbl.Col.value('Gender[1]', 'varchar(10)'),
Tbl.Col.value('City[1]', 'varchar(50)'),
Tbl.Col.value('State[1]', 'varchar(50)')
INTO #xmlTable
FROM   @xml.nodes('/DataTable/Employee') tbl(col)

You can use any method to stage this data and use it. XML is very useful in bulk operations performed from our front end tool. We can use XML variables in procedure input parameter and perform the required operations inside the procedure. Notice that the data type of variable must be XML data type. If you want to explore more about XML data types click here.

Thanks for your reading and please share and rate this post. You can also put your comment/suggestions below.

Rate This
[Total: 0    Average: 0/5]


Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 8 years of industry experience in Software development. I have a head down experience in Database, Data Warehouse, Big Data and cloud technologies and have implemented end to end Database, Data Warehouse,  Big Data and Cloud Solutions.
I have extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). I also have good experience in windows and web application development using ASP.Net and C#.


Leave a comment

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

One thought on “Read XML Data as a table in SQL Server