Systems Engineering and RDBMS

Bulk Loading XML Data

Posted by decipherinfosys on May 20, 2009

Two days ago, as part of a project that we are working on, we got an XML file that was close to 3GB in size.  First attempt was to use openrowset() to dump the XML into a table and then query that data using the nodes() method to parse out the data elements and then do the processing of the data from the staging table(s) into the transactional system.  The openrowset() call went through pretty quickly but working on the XML in the XML data type column in the table was just taking forever.  This was even after the creation of the XML indexes.  Then we decided to use XML Bulk Load and load up the data into the table(s) in the staging area.  One issue was that the client did not know which elements/attributes they will be using in the future so they wanted all of them.

So, using XMLBulkLoad, there is an option of creating the table structure as part of the bulk load and that is what we ended up doing.  It was of course done in a staging area and then once better understanding was there for the data and their relationships, proper table structures with relationships were created.  This is how the code looked like:

Dim objload
Set objload = CreateObject(“SQLXMLBulkLoad.SQLXMLBulkload.4.0″)
objload.ConnectionString = “provider=SQLOLEDB;data source=DIS-03;database=Decipherprod;integrated security=SSPI”
objload.ErrorLogFile = “c:\BulkDataLoad\Load_error.log”

objload.SchemaGen = True
objload.SGDropTables = True

objload.Execute “DataWarehouse-3.4.xml”, “DataWarehouse34_FC_USA_M_20090505.xml”
Set objload = Nothing

References:

  • BOL entry for XML Bulk Load – here.
  • BOL entry for openrowset() – here.
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 74 other followers

%d bloggers like this: