Systems Engineering and RDBMS

Archive for May 20th, 2009

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.

Posted in SQL Server | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers