How to Insert Bulk Records Into SQL Server Using XML Data Type in ASP.Net With C#

Today I will show you how to insert bulk data into a single table in SQL Server using a XML data type.

This article will solve the following problems:

1. Inserting a thousand records using a single connection to the database.
2. Use the XML data type in SQL.

Here we go.

SQL DATABASE

Step 1

Create an “Employee” table as shown in the following figure.



Step 2

Create a Stored Procedure (SP) that will take XML data type as a parameter. This SP will parse XML data and insert it into an “Employee” table. Here is the SP.



ASP.NET

Step 1

This is the insert function that will pass data table as a parameter to the SP. See the following image.



Here is my function that will convert a data table to XML.



Output

Screen 1



Screen 2


Screen 3

Screen 4



NOTE: You can download the code to understand the concept better.