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 
Do you know if it’s possible to use it if ColdFusion and MS SQL are on different servers? I can save the XML file locally on the ColdFusion server, but not on SQL Server, will that work?
Also, for the connectionstring, can a connection somehow be passed in from the coldfusion admin?
We have to move about 10000 records from Oracle DB into MS SQL (nightly). Our Oracle guys wrote a web service that we can access through ColdFusion and it sends back an XML file. That part works really fast (seconds). The file is about 4MB. The ColdFusion then feeds the file to a SQL Server Stored Procedure (I’ll put code below) which is pretty simple and works great on small record sets. I’ve loaded 500 records with it easily. But if we try the full 10k, the SQL Server CPU goes to 100% and never stops. And that’s a powerful server with 4GB of memory.
We’ve even eliminated the first step and just had the file read by coldfusion from disk and passed to MS SQL. The thing is we can’t save it locally to the SQL Server (I guess for security reasons).
So after much searching now I’m trying to figure out if it’s possible to get ColdFusion to work with the SQLXMLBulkLoad object.
Below is the CF and Stored Procedure code (some parts are commented out as we’ve been experimenting with pieces of it.
Thanks for any help,
-Roman
============
MS SQL PART
ALTER PROCEDURE [dbo].[sp_insertOracleFacs] (@xml XML)
AS
BEGIN
DELETE FROM temp_Oracle_Facility_Info
INSERT INTO temp_Oracle_Facility_Info (
facility_id,
company_nm,
section_id,
section_nm,
addr1,
addr2,
addr3,
addr4,
city,
[state],
postal)
SELECT
doc.col.value(‘facility_id[1]‘, ‘varchar(10)’),
doc.col.value(‘company_nm[1]‘, ‘varchar(200)’),
doc.col.value(’section_id[1]‘, ‘varchar(3)’),
doc.col.value(’section_nm[1]‘, ‘varchar(100)’),
doc.col.value(‘addr1[1]‘, ‘varchar(100)’),
doc.col.value(‘addr2[1]‘, ‘varchar(100)’),
doc.col.value(‘addr3[1]‘, ‘varchar(100)’),
doc.col.value(‘addr4[1]‘, ‘varchar(100)’),
doc.col.value(‘city[1]‘, ‘varchar(100)’),
doc.col.value(’state[1]‘, ‘varchar(3)’),
doc.col.value(‘postal[1]‘, ‘varchar(5)’)
FROM @xml.nodes(‘//FacilityInfo’) AS doc(col)
END
===========
COLDFUSION PART
—>
—>
Done Reading File
—>