SQL Server 2017 Hosting :: Save Array Or List Of Records As XML Data To SQL Server In One Call

In this blog, we will discuss the way to submit the data of a model list to SQL Server to save all records of the list at once. We can do this by converting the model list to XML and then that XML can be submitted to SQL Server through Store Procedure. We can read the XML in SQL Server to use the data for SQL queries. I hope this will be helpful in the situations where we need to save multiple records of same entity or model by looping the list.
SQL Server 2017 Hosting
There might be a situation where you have a model named “Student” and you want to save a list of students. Generally, in Entity Framework, we have to iterate the list using for loop and have to save each record one by one. It takes a lot of time to save a large number of records. Even if we have any stored procedure to save single record and use that to save a list by looping, it takes more time. In this case too, the application has to make a connection call each time.
Advantages of using XML post instead of looping
  • Single call to SQL server to save the data
  • Performance improvement as there is no need to make multiple calls to SQL
  • Adding/Removing any field in XML does not require many changes. If you add any property/field in the model list, there is no need to change input paramter of SQL Stored Procedure.
Suppose, we have a model named “Student”.

You can convert your model list into XML like this.

You will have to pass this XML as a string parameter to SQL Server. Suppose the name of the parameter used to pass XML is @data, here is SQL Stored Procedure to save/update the records passed via XML.
 This way, you can insert/update/delete the bulk data according to the requirement in just one SQL call.