Read XML Data as a table in SQL Server

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.

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;

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.