Bulk upload using SQLXML - ExtremeExperts

BulkUpload - SQLXML

There have been loads of third party tools that have evolved in the recent past to transfer and upload data from one system to another. With XML becoming the defacto standards for communication between various layers of an application we have to take this seriously. Biztalk and many other softwares essentially do this to great effect. We have also discussed one similar functionality that SQL Server inherently supplies, OPENXML.

In this article we move one step further in exploring the bulk upload functionality that comes with SQLXML to upload voluminous XML data files into SQL Server 2000. We would also walk through a simple code sample to illustrate its features. You can insert XML data into a SQL Server database by using an INSERT statement and the OPENXML function; however, the bulk load utility provides higher performance when you need to insert large amounts of XML data.

The Execute method of the XML Bulk Load object model takes two parameters:

An annotated XML Schema Definition (XSD). The XML Bulk Load utility interprets this mapping schema and the annotations that are specified in the schema in identifying the SQL Server tables into which the XML data is to be inserted.
An XML document or document fragment (a document without a single top-level element). A file name or a stream from which XML Bulk Load can read can be specified.

So lets get started and know how we can upload XML files into our relational world databases. We need three inputs the relational database, XSD to work with and the XML document to upload.

XSD

A typical XSD used for this example is:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:element name="Customers" sql:relation="Persons" >

<xsd:complexType>

<xsd:sequence>

<xsd:element name="PersonID" type="xsd:integer" />

<xsd:element name="FirstName" type="xsd:string" />

<xsd:element name="LastName" type="xsd:string" />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>

We map the XMLs Customers tag to Persons table in the SQL Server and then upload each of the data into the table.

XML File

<ROOT>

<Customers>

<PersonID>1</PersonID>

<LastName>Vinod</LastName>

<FirstName>Kumar</FirstName>

</Customers>

<Customers>

<PersonID>2</PersonID>

<LastName>Saravana</LastName>

<FirstName>Kumar</FirstName>

</Customers>

</ROOT>

We have the sample file also to upload. Since we have mapped to the database table we need a table to upload the same. Use the following script to create the table.

CREATE TABLE Persons

(PersonID int PRIMARY KEY,

LastName Varchar(30),

FirstName Varchar(30))

Uploading

So to upload we have to instantiate the XML Bulk Load object and pass these two file and execute the command. Its as simple as that.

        Dim objBL As Object

        objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")

        objBL.ConnectionString = "provider=SQLOLEDB.1;data source=localhost;database=Northwind;" & _

                "integrated security=SSPI"

        objBL.Execute("Schema.xml", "XMLData.xml")

        objBL = Nothing

After executing the above code you can query the Persons table to find that the two rows from the XML File has been uploaded into the database.

Conclusion

We have just touched the surface by executing a simple code block to do the upload process. There are tons and tons of variety in using this XML Bulk Upload functionality. We would discuss them as and when time permits in the future. You can refer to the SQLXML Online documentaiton for more technical and complecated scenarios.