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', 'smallint'), Tbl.Col.value('Name', 'varchar(100)'), Tbl.Col.value('Age', 'smallint'), Tbl.Col.value('Gender', 'varchar(10)'), Tbl.Col.value('City', 'varchar(50)'), Tbl.Col.value('State', 'varchar(50)') FROM @xml.nodes('/DataTable/Employee') tbl(col)
This select statement will output as below;
Now we can put … More