I thought I would post this to help anybody else who might be struggling with loading large XML files and datasets into Microsoft SQL using ColdFusion.

Using this technique, you can use a Microsoft COM object called “SQLXMLBulkLoad.SQLXMLBulkLoad”. This is a very fast object that allows you to provide an XML mapping file, the XML file and then import the records from the XML directly into the database table.

In order to use this in ColdFusion you might use something like this ….

<cfscript>
objS = CreateObject(”com”,”SQLXMLBulkLoad.SQLXMLBulkLoad”);
objS.ConnectionString = “provider=sqloledb;server=[server name];database=[db];uid=[user];pwd=[password];”;
objS.ErrorLogFile = “C:\mypath\mydirectory\channelerror.log”;
objS.Execute(’C:\mypath\mydirectory\mapping.xml’, ‘C:\mypath\mydirectory\data.xml’);
ReleaseComObject(objS);
</cfscript>

That’s it. Very simple and like I said, it’s really quick! Enjoy