INSERTING DATA IN XML FORMAT TO THE TABLES IN THE DATABASE

Hi All,

In my previous blog post I explained how to convert the data in a DataTable to SqlXml format to send in to the database. So in this post I will explain how to insert that data into the tables of the database. Here I'm using SQL Server 2008 R2 as my database.

I will be using the same XML which was generated in my previous blog post.

XML to be inserted

So first up I will create a table in sql server to store this information.







OK so now since we have the table created now lets insert the data to this table. I will write a stored procedure which accepts an XML as a parameter and then insert the data to the Student table through the stored procedure.

Stroed Procedure to insert the XML Data To the Student Table

The stored procedure accepts the student details as an XML parameter. The this XML is parsed using the MSXML parser, which makes the XML ready for consumption. You can read more on sp_xml_preparedocument from here.

So once the XML ready then I can select the data from the XML using OPENXML and then insert the data to the Student table.

Next we can call the Stored Procedure by passing the XML.

Calling the strored procedure by passing the XML

So if we query the student table now, we can see the data inserted.

Data Inserted to the Student Table

That's all for this post.
Happy Coding.