SQLXMLBulkLoad Rocks!

For reasons that will become obvious in September (sly grin), I've been working a lot lately on uploading data to SQL Server 2005. There are a variety of mechanisms you can use to do this, but all of them have their shortcomings under certain conditions. For example, using a series of INSERT statements is slow while bulk insert doesn't work with memo fields. While looking for something else in SQL Server Books Online, I happened across a topic on bulk XML loading. After playing with it for a while, it seemed pretty easy to do, so I did some testing on a relatively large file (365,741 records) containing memo fields (which means I couldn't use bulk insert). Using other mechanisms, it took more than two hours to load the data into a SQL Server table. Using bulk XML load, it took 11 minutes. That's a 90% improvement in speed. I love making things faster!

Using bulk XML load is easy: you instantiate SQLXMLBulkLoad.SQLXMLBulkload.4.0, set its ConnectionString property to an OLE DB connection string, set some other properties appropriately (for example, KeepNulls to .T. to insert null rather than default values for missing column values), and call Execute, passing it the name of a schema file and the name of the XML data file. Execute throws an error if there's something wrong with either file, and import errors are logged to a file whose name is in the ErrorLogFile property.

There's one slight wrinkle in working with VFP data: while the CURSORTOXML() function can create a schema file, it needs to be tweaked slightly to work with bulk XML load. Also, DateTime fields have to be flagged with the sql:datatype="dateTime" attribute (the latter was documented, the former took some trial and error to resolve).

Here's a generic program that will load the data from an open VFP cursor into an existing SQL Server table. You can download it from http://www.stonefield.com/articles/other/bulkxmlload.zip.

*==============================================================================
* Function:        BulkXMLLoad
* Purpose:         Performs a SQL Server bulk XML load
* Author:          Doug Hennig
* Last revision:   07/06/2006
* Parameters:      tcAlias    - the alias of the cursor to export
*                  tcTable    - the name of the table to import into
*                  tcDatabase - the database the table belongs to
*                  tcServer   - the SQL Server name
*                  tcUserName - the user name for the connection (optional:
*                    if it isn't specified, Windows Integrated Security is
*                    used)
*                  tcPassword - the password for the connection (optional:
*                    if it isn't specified, Windows Integrated Security is
*                    used)
* Returns:         an empty string if the bulk load succeeded or the text of
*                    an error message if it failed
* Environment in:  the alias specified in tcAlias must be open
*                  the specified table and database must exist
*                  the specified server must be accessible
*                  there must be enough disk space for the XML files
* Environment out: if an empty string is returned, the data was imported into
*                    the specified table
*==============================================================================


lparameters tcAlias, ;
  tcTable, ;
  tcDatabase, ;
  tcServer, ;
  tcUserName, ;
  tcPassword
local lnSelect, ;
  lcSchema, ;
  lcData, ;
  lcReturn, ;
  loException as Exception, ;
  lcXSD, ;
  loBulkLoad


* Create the XML data and schema files.


lnSelect = select()
select (tcAlias)
lcSchema = forceext(tcTable, 'xsd')
lcData   = forceext(tcTable, 'xml')
try
  cursortoxml(alias(), lcData, 1, 512 + 8, 0, lcSchema)
  lcReturn = ''
catch to loException
  lcReturn = loException.Message
endtry


* Convert the XSD into a format acceptable by SQL Server. Add the SQL
* namespace, convert the  start and end tags to ,
* use the sql:datatype attribute for DateTime fields, and specify the table
* imported into with the sql:relation attribute.


if empty(lcReturn)
  lcXSD = filetostr(lcSchema)
  lcXSD = strtran(lcXSD, ':xml-msdata">', ;
    ':xml-msdata" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">')
  lcXSD = strtran(lcXSD, 'IsDataSet="true">', ;
    'IsDataSet="true" sql:is-constant="1">')
  lcXSD = strtran(lcXSD, '<xsd:choice maxOccurs="unbounded">', ;
    '<xsd:sequence>')
  lcXSD = strtran(lcXSD, '</xsd:choice>', ;
    '</xsd:sequence>')
  lcXSD = strtran(lcXSD, 'type="xsd:dateTime"', ;
    'type="xsd:dateTime" sql:datatype="dateTime"')
  lcXSD = strtran(lcXSD, 'minOccurs="0"', ;
    'sql:relation="' + lower(tcTable) + '" minOccurs="0"')
  strtofile(lcXSD, lcSchema)


* Instantiate the SQLXMLBulkLoad object, set its ConnectionString and other
* properties, and call Execute to perform the bulk import.


  try
    loBulkLoad = createobject('SQLXMLBulkLoad.SQLXMLBulkload.4.0')
    lcConnString = 'Provider=SQLOLEDB.1;Initial Catalog=' + tcDatabase + ;
      ';Data Source=' + tcServer + ';Persist Security Info=False;'
    if empty(tcUserName)
      lcConnString = lcConnString + 'Integrated Security=SSPI'
    else
      lcConnString = lcConnString + 'User ID=' + tcUserName + ;
        ';Password=' + tcPassword
    endif empty(tcUserName)
    loBulkLoad.ConnectionString = lcConnString
*** Can set the ErrorLogFile property to the name of a file to write import
*** errors to
    loBulkLoad.KeepNulls        = .T.
    loBulkLoad.Execute(lcSchema, lcData)
    lcReturn = ''
  catch to loException
    lcReturn = loException.Message
  endtry


* Clean up.


  erase (lcSchema)
  erase (lcData)
endif empty(lcReturn)
select (lnSelect)
return lcReturn