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

• SQL table and XML
• SQL Table structure

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.

<Employees>
    <Employee Id ="1">
        <Name>Sagar Shinde</Name>
        <Country>United States</Country>
        <Others>
            <Role>TL</Role>
            <Level>1</Level>
        </Others>
    </Employee>
    <Employee Id = "2">
        <Name>Swapnil Shinde</Name>
        <Country>India</Country>
        <Others>
            <Role>AM</Role>
            <Level>2</Level>
        </Others>
    </Employee>
    <Employee Id ="3">
        <Name>Pankaj Shinde</Name>
        <Country>France</Country>
        <Others>
            <Role>SSE</Role>
            <Level>3</Level>
        </Others>
    </Employee>
</Employees>

SQL query to insert xml bulk record 

Declare @xml XML='

<Employees>
    <Employee Id ="1">
        <Name>Sagar Shinde</Name>
        <Country>United States</Country>
        <Others>
            <Role>TL</Role>
            <Level>1</Level>
        </Others>
    </Employee>
    <Employee Id = "2">
        <Name>Swapnil Shinde</Name>
        <Country>India</Country>
        <Others>
            <Role>AM</Role>
            <Level>2</Level>
        </Others>
    </Employee>
    <Employee Id ="3">
        <Name>Pankaj Shinde</Name>
        <Country>France</Country>
        <Others>
            <Role>SSE</Role>
            <Level>3</Level>
        </Others>
    </Employee>
</Employees>
'
INSERT INTO EmployeeDetails
SELECT
Emp.detail.value('@Id','INT') AS Id, --ATTRIBUTE
Emp.detail.value('(Name/text())[1]','VARCHAR(100)') AS Name, --TAG
Emp.detail.value('(Country/text())[1]','VARCHAR(100)') AS Country, --TAG
Convert(nvarchar(max),Emp.detail.query('Others')) AS Others --XML Node
FROM
@xml.nodes('/Employees/Employee')AS Emp(detail)

Output

In the above query, we have used XML Data Type method. Value, query, etc., below, are the all methods that are used in XML data type.

Method Name	Description	Syntax
query()	Describes how to use the query() method to query over an XML instance.	query ('XQuery')
value()	Describes how to use the value() method to retrieve a value of SQL type from an XML instance.	value (XQuery, SQLType)
exist()	Describes how to use the exist() method to determine whether a query returns a nonempty result.	exist (XQuery)
modify()	Describes how to use the modify() method to specify XML Data Modification Language (XML DML)statements to perform updates	modify (XML_DML)
nodes()	Describes how to use the nodes() method to shred XML into multiple rows, which propagates parts of XML documents into row sets.	nodes (XQuery) as Table(Column)
Binding Relational Data Inside XML Data	Describes how to bind non-XML data inside XML.
Guidelines for Using xml Data Type Methods	Describes guidelines for using the xml data type methods.

Conclusion

I hope you understand this concept. Thanks for reading.  Enjoy!