Implement SQLXML Bulk Load using C++

01 Dec 2005
Saint Paul, Minnesota
[email protected]

Hi ...

I am trying to bulk load xml into sql server. This seems to be a relativly easy thing to do using the SQLXML COM Component. I am having trouble finding documentation on the SQLXML COM Object model. Also I have found samples in C# and VB ... I need my implementation to be using C++. If you have any sample code or a like you can send me to that would be create.

A good example of the type of doc that I am looking for is the object model for the MSXML COM Component
http://msdn.microsoft.com/library/de...interfaces.asp
Is there such documentation for the SQLXML COM

Below are the links I have used to date:
http://msdn.microsoft.com/library/de...nch_sqlxml.asp
http://www.dbazine.com/sql/sql-articles/cook3
http://www.perfectxml.com/sqlxml.asp

Some code:

The code below uses the IXMLDOMDocument2Ptr interface and then trys to
use the SQLXML using the SQLXMLOLEDB Provider. Again, I need some doc
or samples on how to use the SQLXML COM Interfaces or the
SQLXMLOLEDB Provider in C++.

The code will fail on the following line
pCmd.CreateInstance(__uuidof(SQLXMLBulkLoad));


Code:
void CTestApp::OnTestMsXmlTest()
{
    // Define ADO connection pointers
    _ConnectionPtr pConnection = NULL;

    try
    {

        CoInitialize(NULL);
        IXMLDOMDocument2Ptr pXMLDoc = NULL;
        TCHAR   szHTTPURL[MAX_PATH] = {0};

        _tcscpy(szHTTPURL, "http://www.buckeyetraffic.org/rwis/data/siteDetailS.xml");
        _variant_t varLoadResult((bool)FALSE);

        // Create MSXML DOM object
        pXMLDoc.CreateInstance("Msxml2.DOMDocument.4.0");

        // Load the document synchronously
        pXMLDoc->async = false;

        // If loading the document over HTTP (see KB Q321125 for details)
        pXMLDoc->setProperty("ServerHTTPRequest", VARIANT_TRUE);

        // Load the XML document
        varLoadResult = pXMLDoc->load(szHTTPURL);

        // Get the xml data
        csResult =::SysAllocString(pXMLDoc->xml);


        // ********************
        // Test the buld load things

        // Create the ADO connection
        pConnection.CreateInstance(__uuidof(Connection));

        CString csProvider("sqloledb");
        CString csDataSource("SQLTEST1");
        CString csInitialCatalog("TestData");
        CString csUserId("bill");
        CString csPassword("bluerayon");

        // Set the connection string
        csConnect.Format("Provider='%s';Data Source='%s';",
                          csProvider,
                          csDataSource);
        csTemp.Format("Initial Catalog='%s';",
                      csInitialCatalog);
        csConnect += csTemp;
        csTemp.Format("User Id=%s;Password=%s",
                      csUserId,
                      csPassword);
        csConnect += csTemp;

        bstrConnect = csConnect.AllocSysString();

        // Write log to view
        pView->WriteLog("Open ADO Connection.");

        // Open the ado connection
        pConnection->Open(bstrConnect,"","",adConnectUnspecified);

        // Define ADO object pointers.
        _CommandPtr      pCmd = NULL;

        // Create command object
        pCmd.CreateInstance(__uuidof(Command));
        // pCmd.CreateInstance(__uuidof(SQLXMLBulkLoad));
        // pCmd.CreateInstance(__uuidof(SQLXMLBulkLoad.SQLXMLBulkLoad.2.0));

        pCmd->ActiveConnection = pConnection;
        pCmd->PutPrepared(true);

        pCmd->Execute("SampleSchema.xml", "SampleXMLData.xml", adExecuteStream);

    }
    catch(...)
    {
        // Handle Error
    }

    CoUninitialize();
    return;
}