Binaryworld - How to load validate and load XML file into sql server database using ... [ SQL Server -> XML ]

Step-1: Create table in which you want to load XML file

create database MyDB
go
USE MyDB
go
CREATE TABLE Customer (
CustomerId INT PRIMARY KEY,
CompanyName NVARCHAR(20),
City NVARCHAR(20))
go
--After insert run this
select * from Customer

Step-2: create xml file - customers.xml

Click here to copy the following block

<ROOT>
<Customers>
  <CustomerId>1111</CustomerId>
  <CompanyName>Sean Chai</CompanyName>
  <City>NY</City>
</Customers>
<Customers>
  <CustomerId>1112</CustomerId>
  <CompanyName>Tom Johnston</CompanyName>
  <City>LA</City>
</Customers>
<Customers>
  <CustomerId>1113</CustomerId>
  <CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>

Step-3: create xml schema file for validation - customermapping.xsd

Click here to copy the following block

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
    xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
    xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

<ElementType name="CustomerId" dt:type="int" />
<ElementType name="CompanyName" dt:type="string" />
<ElementType name="City" dt:type="string" />

<ElementType name="ROOT" sql:is-constant="1">
   <element type="Customers" />
</ElementType>

<ElementType name="Customers" sql:relation="Customer">
   <element type="CustomerId" sql:field="CustomerId" />
   <element type="CompanyName" sql:field="CompanyName" />
   <element type="City"    sql:field="City" />
</ElementType>
</Schema>

Step-4: create following vbscript file in notepad - name it insert_xml_data.vbs

Click here to copy the following block

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=(local);database=MyDB;uid=etl;pwd=etl"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "c:\customermapping.xsd", "c:\customers.xml"
Set objBL = Nothing

Finally double click saved vbs file to execute.