Using SQLXML, Bulk Insert XML with IDENTITY Column ...

[cmacgowan]

Using SQLXML, Bulk Insert XML with IDENTITY Column ... I have a program that will insert xml data into a table using the SQLXML Bulk Load COM Object. The bulk load is successful when I supply the RecordId in the xml. When I add an IDENTITY column to the table then the bulk load fails with the following error: [Cannot insert the value NULL into column 'RecordId', table 'Alphanumericdata.dbo.MacgowanTestCust'; column does not allow nulls. INSERT fails.] From the following article, XML Bulk Load ignores elements and attributes that are not mapped (either because they are not described in the schema, or because they are annotated in the XSD schema with sql:mapped="false"). All unmapped data goes into the overflow column, if such a column is specified by using sql:overflow-field. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload\_9w9w.asp I have attempted using sql:mapped="false" in the schema file and not using it .. both fail with the “nulls not allowed” error. I have also set the KeepIdentity(true) to my pISQLXMLBulkLoad object. Below is the table, xml, xsd and code ... Any comments or answers are appreciated. Thanks, Chris /////////////////////////////////////////////////// // The code char progID[] = "SQLXMLBulkLoad.SQLXMLBulkload.3.0"; CLSID clsid; wchar_t wide[80]; mbstowcs(wide, progID, 80); CLSIDFromProgID(wide, &clsid); ISQLXMLBulkLoad* pISQLXMLBulkLoad = NULL; if(SUCCEEDED(CoCreateInstance(clsid, NULL, CLSCTX_ALL, IID_ISQLXMLBulkLoad, (void**)&pISQLXMLBulkLoad))) { hResult = pISQLXMLBulkLoad->put_ConnectionString(bstrConnect); hResult = pISQLXMLBulkLoad->put_ErrorLogFile(bstrXmlErrorLogFile); hResult = pISQLXMLBulkLoad->put_KeepIdentity((bool)TRUE); hResult = pISQLXMLBulkLoad->Execute(bstrXmlSchemaFile, vXmlDataFile); } /////////////////////////////////////////////////// // xml data (successful) RecordId is included 1 1111 Sean Chai NY 2 1112 Tom Johnston LA 3 1113 Institute of Art /////////////////////////////////////////////////// // xsd schema file (successful) RecordId is included