SQL Server XML Bulk load examples

This article is about bulk loading XML file into tables (shredding XML data into columns of table)  :
i) Loading single table
ii)Loading multiple tables (With Foreign key relationship)
iii)Handling identity column insert while loading XML file
iv)Handling table creation before loading XML file

Prerequisites :  (Install SQLXML 4.0 SP1)

As mentioned in MSDN , after SQL Server 2008, the latest version of SQLXML (SQLXML 4.0 SP1) is no longer included in SQL Server. To install SQLXML 4.0 SP1 when it is available, download it from  - http://www.microsoft.com/en-gb/download/details.aspx?id=30403

Step by step Installation guide for SQLXML 4.0 SP1 - 

i) Example : Loading single table

Step 1 : Create table

USE [AdventureWorks2012]
GO
CREATE TABLE Student (Id INT , Name VARCHAR(100))
 
Step 2 : Open notepad and place the XML and save it as Studxml.xml

<StudentInfo>
  <Student>
    <Id>1111</Id>
    <Name>Sathya</Name>
  </Student>
  <Student>
    <Id>1112</Id>
    <Name>Deepak</Name>
  </Student>
</StudentInfo>


Step 3 : Open notepad and place the XSD - XML schema and save it as StudXSD.xml

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
   <xsd:element name="StudentInfo" sql:is-constant="1" >
     <xsd:complexType>
       <xsd:sequence>
         <xsd:element name="Student" sql:relation="Student" maxOccurs="unbounded">
           <xsd:complexType>
             <xsd:sequence>
               <xsd:element name="Id"  type="xsd:integer" />
               <xsd:element name="Name" type="xsd:string" />
             </xsd:sequence>
           </xsd:complexType>
         </xsd:element>
       </xsd:sequence>
      </xsd:complexType>
     </xsd:element>
</xsd:schema>

How to: Create an XML Schema from an XML Document - http://msdn.microsoft.com/en-us/library/ms255829.aspx



Step 4 : Open notepad and place the VB script file (to bulk load XML file data into columns of table) and save it as Exec.vbs

note : ( Change the data source and database in below script)

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.ConnectionString = "provider=SQLOLEDB;data source=Sathya-PC;database=AdventureWorks2012;integrated security=SSPI"
objBL.Execute "StudXSD.xml", "Studxml.xml"


Step 5 : Now click on Exec.vbs and check the table

ii)Example  : Loading multiple tables (With Foreign key relationship)

Step 1 : Create tables 

USE [AdventureWorks2012]
GO

DROP TABLE  Student

CREATE TABLE Student (Id INT NOT NULL CONSTRAINT pk_StudentId PRIMARY KEY (Id) , Name VARCHAR(100)) ;

CREATE TABLE StudentGrade(StudentId INT FOREIGN KEY REFERENCES Student(Id),Grade CHAR(10));


Step 2 : Open notepad and place the XML and save it as StudGradeXML.xml

<StudentInfo>
  <Student>
    <Id>1111</Id>
    <Name>Sathya</Name>
    <StudentGrade Grade= "A" />
  </Student>
  <Student>
    <Id>1112</Id>
    <Name>Deepak</Name>
    <StudentGrade Grade= "D" />
  </Student>
</StudentInfo>


Step 3 : Open notepad and place the XSD - XML schema and save it as StudGradeXSD.xml

 <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:annotation>
    <xsd:appinfo>
      <sql:relationship name="pk_StudentId"
          parent="Student"
          parent-key="Id"
          child="StudentGrade"
          child-key="StudentId" />
    </xsd:appinfo>
  </xsd:annotation>
  <xsd:element name="StudentInfo" sql:is-constant="1" >
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="Student" sql:relation="Student" >
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="Id"  type="xsd:integer" />
              <xsd:element name="Name" type="xsd:string" />
              <xsd:element name="StudentGrade"
                          sql:relation="StudentGrade"
                          sql:relationship="pk_StudentId" >
                <xsd:complexType>
                  <xsd:attribute name="Grade" type="xsd:string" />
                </xsd:complexType>
              </xsd:element>
             </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>


Step 4 : Open notepad and place the VB script file (to bulk load XML file data into columns of multiple tables) and save it as StudGradeExec.vbs

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.ConnectionString = "provider=SQLOLEDB;data source=Sathya-PC;database=AdventureWorks2012;integrated security=SSPI"
objBL.Execute "StudGradeXSD.xml", "StudGradeXML.xml"

 
Step 5 : Now click on StudGradeExec.vbs and check the tables

note: we have defined the parent & child table relationships in XML schema , so it will 
automatically load into respective tables .

iii)Example : Handling identity column insert while loading XML file

Step 1 : create table 

USE [AdventureWorks2012]
GO
DROP TABLE StudentGrade
DROP TABLE Student

CREATE TABLE Student (Id INT IDENTITY(1,1),Name VARCHAR(50)) 


Step 2 : Let us re-use the XML file & Schema file  used in first example in this article 


Step 3 : Open notepad and place the VB script file (to test identity property) and save it as IdentityTestExec.vbs

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.ConnectionString = "provider=SQLOLEDB;data source=Sathya-PC;database=AdventureWorks2012;integrated security=SSPI"
objBL.KeepIdentity = False
objBL.Execute "StudXSD.xml", "Studxml.xml"

 
Step 4 : Now click on IdentityTestExec.vbs and check the identity column of the table .

Id value from the XML file is ignored and the Identity value of the table is inserted .

note :
If the execution script has this setting objBL.KeepIdentity = True  then we can insert Id value from XML file ignoring the default identity property of the table .

It is like 
SET IDENTITY_INSERT Student ON 

 iv)Example : Handling table creation before loading XML file

Step 1 : Drop the table which we have previously created 

USE [AdventureWorks2012]
GO
DROP TABLE Student


Step 2 : Let us re-use the XML file & Schema file  used in first example in this article 

Step 3 : Open notepad and place the VB script file (to test creation of table if it does not exist while bulk loading XML file ) and save it as SchemaGenExec.vbs

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.ConnectionString = "provider=SQLOLEDB;data source=Sathya-PC;database=AdventureWorks2012;integrated security=SSPI"
objBL.SchemaGen = True
objBL.Execute "StudXSD.xml", "Studxml.xml"

 Step 4 : Now click on SchemaGenExec.vbs and check the database (table will be created
and loaded with data from XML file)

note :
If the execution script has this setting objBL.SGDropTables = True   then it will drop the
table if it already  exists in the database .

Related reference links :
http://msdn.microsoft.com/en-us/library/cc645615.aspx
http://www.microsoft.com/en-gb/download/details.aspx?id=30403
http://msdn.microsoft.com/en-us/library/ms171806.aspx

Note : We can bulk load XML files of big size more efficiently using the above discussed method in this article , just to show examples I have used small XML document .