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

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/de...kload_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

Code:

---

///////////////////////////////////////////////////
//  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
<ROOT>
  <Customers>
    <RecordId>1</RecordId>
    <CustomerID>1111</CustomerID>
    <CompanyName>Sean Chai</CompanyName>
    <City>NY</City>
  </Customers>
  <Customers>
    <RecordId>2</RecordId>
    <CustomerID>1112</CustomerID>
    <CompanyName>Tom Johnston</CompanyName>
    <City>LA</City>
  </Customers>
  <Customers>
    <RecordId>3</RecordId>
    <CustomerID>1113</CustomerID>
    <CompanyName>Institute of Art</CompanyName>
  </Customers>
</ROOT>
 
///////////////////////////////////////////////////
//  xsd schema file (successful) RecordId is included
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
 
  <xsd:element name="Customers" sql:relation="MacgowanTestCust" >
  <xsd:complexType>
    <xsd:sequence>
      <xsd:element name="RecordId"    type="xsd:integer" sql:field="RecordId" />
      <xsd:element name="CustomerID"  type="xsd:integer" sql:field="CustomerID" />
      <xsd:element name="CompanyName" type="xsd:string"  sql:field="CompanyName" />
      <xsd:element name="City"        type="xsd:string"  sql:field="City" />
    </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
 
///////////////////////////////////////////////////
//  xml data (fails) attempting to use identity column
<ROOT>
  <Customers>
    <CustomerID>1111</CustomerID>
    <CompanyName>Sean Chai</CompanyName>
    <City>NY</City>
  </Customers>
  <Customers>
    <CustomerID>1112</CustomerID>
    <CompanyName>Tom Johnston</CompanyName>
    <City>LA</City>
  </Customers>
  <Customers>
    <CustomerID>1113</CustomerID>
    <CompanyName>Institute of Art</CompanyName>
  </Customers>
</ROOT>

///////////////////////////////////////////////////
//  xsd schema file (fails) attempting to use identity column
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
 
  <xsd:element name="Customers" sql:relation="MacgowanTestCust" >
  <xsd:complexType>
    <xsd:sequence>
      <xsd:element name="CustomerID"  type="xsd:integer" sql:field="CustomerID" />
      <xsd:element name="CompanyName" type="xsd:string" sql:field="CompanyName" />
      <xsd:element name="City"        type="xsd:string" sql:field="City" />
    </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
 
 
///////////////////////////////////////////////////
//  table
CREATE TABLE [MacgowanTestCust] (
    [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
    [CustomerID] [int] NOT NULL ,
    [DataSourceId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestCust_DataSourceId] DEFAULT ('OH'),
    [CompanyName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [City] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    PRIMARY KEY  CLUSTERED
    (
        [RecordId]
    )  ON [PRIMARY]
) ON [PRIMARY]
GO
 
 
///////////////////////////////////////////////////
//  error message
<?xml version="1.0"?><Error><Record><HResult>0x80004005</HResult><SQLState>01000</SQLState><NativeError></NativeError><ErrorState>1</ErrorState><Severity>0</Severity><Source>Microsoft OLE DB Provider for SQL Server</Source><Description><![CDATA[The statement has been terminated.]]></Description></Record><Record><HResult>0x80004005</HResult><SQLState>23000</SQLState><NativeError></NativeError><ErrorState>2</ErrorState><Severity>16</Severity><Source>Microsoft OLE DB Provider for SQL Server</Source><Description><![CDATA[Cannot insert the value NULL into column 'RecordId', table 'Alphanumericdata.dbo.MacgowanTestCust'; column does not allow nulls. INSERT fails.]]></Description></Record></Error>

---