How To Insert Bulk Records Into SQL Server Using XML Data Type

Introduction

In this article, we will see:

• What is the business requirement to insert bulk data into the database?
• How many ways are there to insert bulk data into the database?
• How do we insert bulk records into SQL Server using XML Data Type?

Background

Sometimes, there is a need to insert bulk records into a database. In some cases, we have multiple unique records that all need to be inserted into the database. Instead of hitting the database for each record, we need to insert bulk records into the database.

How many ways are there to insert bulk data into the database?

There are basically two ways to send bulk data:

1. Create user defined type in database and send your whole table to this type. SQL query can read every record and perform insert action to the database.
2. Using XML data type, pass XML to the database.

In this article, we will see how to insert bulk records using XML data type.

Insert Bulk Records into SQL Server using XML Data Type

What is XML data type in SQL?

XML data type is used to work with XML data. Using this data type, we can store XML in its native format and can also query/modify the xml data within the xml. We can use xml data types such as: Variable, Field/Column in a table, Parameter in the user-defined function (UDF) or stored procedure (SP), return value from a UDF or SP.

We can define xml data type field to NOT NULL or we can provide a default value to it.

Now, let’s see an example.

Prerequisite

I have created a simple table named EmployeeDetails whose schema is shown below.

XML

I have created a simple xml whose schema is shown below.

SQL query to insert xml bulk record

Declare @xml XML='