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.

CONVERTING DATA IN A DATATABLE TO SQLXML TO SEND TO THE DATABASE

Hi All,

Today I'm going to explain on how to convert the data in a DataTable to SqlXml. This would be helpful when you have lots of records in a DataTable, and you need to insert them at one go rather than inserting each row one at a time.

So I will create a small console based application to demonstrate this. I will show 2 ways on how this can be done. But I'm not sure about the performance in both the approaches.

First up I will create a DataTable and add some columns to it. Remember when you create the DataTable you have to give a name to it. Otherwise the XML cannot be created.

Creating the DataTable

I have named my DataTable as "SampleTable". Then I add some records to this.

Adding Records to the DataTable

So I inserted 2 records to the DataTable. To create the XML I will be using a stream. The stream that I'm going to use is a MemoryStream. DataTable has a method called WriteXml() which has 16 overloaded versions. I will use the overloaded version of the method which accepts a stream object.

Writing the data in the DataTable to the stream

Now we have the stream. I will explain 2 ways on how to convert this stream to SqlXml.

Method 1:

This is the most simplest way of doing it. I can create a object of type SqlXml by passing the stream object to the constructor. Remember to Flush and Close the stream once you have finished working with it.

Creating the SqlXml from the stream

Output :

Method 2 :

The next method is to use a XmlTextReader. Then from the XmlTextReader object I construct the SqlXml object.

Creating the SqlXml from the XmlTextReader

Output :

Both the outputs are the same. This XML can be sent into a stored procdeure in the database to insert the data to the tables.

Thats all for this post.
Happy Coding.