Using SQL Bulk Load to import XML

A Programmers Journal - Using SQL Bulk Load to import XML

Importing very large XML files into SQL server can be taxing for standard methods. Thats where SQL Bulk load comes in very handy, Bulk load can load a 100 meg XML File into a SQL Server table in a matter of seconds. Using the microsoft standard xml parser in your application could leave you hanging could leave you hanging for over 10 minutes with a file this size. And to make things even better bulk load is actually quite simple to use.
To get started using bulk load you will need to install the Web Services Toolkit and the SQL Server Client tools on the machine that you will be running bulk load on. You do not have to install any additional components on your sql server itself. So you can simply install the toolkit on your desktop and run your bulk inserts from there.
There are two methods you can use to code your bulk load, either in a DTS or in an application. Both are relatively the same, I will outline bulk loading using VB6(visual basic 6) for this example.
Once you have the xml toolkit installed you will find a reference to Microsoft SQLXML BulkLoad 3.0 Type Library (xblkld3.dll) available. You will need to check this option to run bulk load. Next insert the code below into you application.
Attribute VB_Name = "bulkload"
Option Explicit
Sub Main()
Dim objXBL As New SQLXMLBulkLoad3
objXBL.ConnectionString = "PROVIDER=SQLOLEDB.1;SERVER=[SERVER];UID=[USER];PWD=[PASS];
DATABASE=[DBNAME];"
objXBL.ErrorLogFile = "errlog.txt"
objXBL.KeepIdentity = False
objXBL.Execute "xsdfile.xsd", "xmlfile.xml"
Set objXBL = Nothing
End Sub
You will see that you need to provide a connection string to your database, I am using an OLE connections tring here but a ODBC will work as well. You will also notice that you must provide two files, first the XML file to be imported and an XSD file for translation. An XSD transformation is very similar to the XSL transformations you may use very often but instead of outputting the XML to another XML file or HTML output an XSD outputs directly to SQL Server. The XSD model allows for addition keys that will allow you to specify tables, column names and other database objects. Below is an example of the contents of your XSD file.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="product" sql:relation="table_1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="localkey" type="xsd:string" sql:field="localkey" />
<xsd:element name="name" type="xsd:string" sql:field="name" />
<xsd:element name="description" type="xsd:string" sql:field="description" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="category" sql:relation="table_2" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="path" type="xsd:string" sql:field="path" />
<xsd:element name="id" type="xsd:string" sql:field="_ID" />
<xsd:element name="description" type="xsd:string" sql:field="localeDescriptions_itemDescription_description" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
As you can see you can specify the tables and columns that data should insert into by defining the XML element and mapping it to a database field.
Though the XSD model does allow of multi-tiered XML files, please note that SQL Server Bulk load will crash if you try to load this type of XML file. If the XML file that you are dealing with is multi-teared you will first need to run an XSL tranformation to flatten XML file down to one level. I have included an example XSL file in my application that performs this task.
Application Source: bulkload.rar
Related articles:
Using SQL Server's XML Support
SQL Server 2000 Incremental Bulk Load Case Study
SQLXML BulkLoad vs. bcp
Importing XML into SQL Server 2000
SQL Server 2000 XML and .NET Integration Features