Sunday, March 25, 2012

Bulk Insert XML with IDENTITY Column

Hi ...
I have a program that will insert xml data into a table. When I add an IDENTITY column to the table then I get the following error:
... [Cannot insert the value NULL into column 'RecordId', table 'Alphanumericdata.dbo.MacgowanTestCust'; column does not allow nulls. INSERT fails.]
Reading another article here I have added the KeepIdentity(true) to my pISQLXMLBulkLoad object.
Below is the table, xml, xsd and code ...
Any comments 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
<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>

///////////////////////////////////////////////////
// xsc schema file
<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]
GOHi ...
To allow SQL Server to set the IDENTITY column the KeepIdentity atribute needs to be set to false (KeepIdentity((bool)FALSE).
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)FALSE);
hResult = pISQLXMLBulkLoad->Execute(bstrXmlSchemaFile, vXmlDataFile);
}

No comments:

Post a Comment