How to insert multiple records using XML in SQL server 2008

Question

i use SQL server 2008 in my web application back end. Apparently i iterate through all the records from the C# code whenever there is a multiple insertion scenario. i have never tried the multple insertion using XML. And i think after reading many blogs about XML manipulation using SQL server 2008 the process is pretty tideous .. So my question is..

• Is Insertion via XML much efficient than the traditional insertion?
• Is the a generic way in which i can serialize a class in C# --> Manipulate XML in SQL -->Insert also Read data as XML--> Deserialize XML to usual object?

Answer 1

The whole "inserting multiple records with XML" has pretty much been superseded by table parameters in SQL Server 2008.

And mentioned in Erland Erland Sommarskog's Arrays and Lists in SQL Server 2008: the definitive article on this subject.

.net has better XML handling than the SQLXML implementation too.

Answer 2

While TVPs would be a better solution, this can be done via XML as well. I talked about it a while back in an article and my blog.

Answer 3

You might want to look into Table-Valued Parameters instead.