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:

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

Here we go.

SQL DATABASE

Step 1

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

employee table

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.

Store Procedure

ASP.NET

Step 1

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

convert datatable to XML

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

convert data table to xml

Output

Screen 1

select query

Screen 2

insert data
Screen 3
data inseri
Screen 4

select query in sql

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