Prevent duplicates when insert by SQLXMLBULKLOADLib

[NiceStepUp]

Prevent duplicates when insert by SQLXMLBULKLOADLib

I am wondering if there is way to prevent insert of duplicate rows while we use SQLXMLBULKLOADLib. I've tried to add UNIQUE NONCLUSTERED INDEX to the SQL table, however insert by SQLXMLBulkLoad4Class fails.

So my code looks likes this:

SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class objBL = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();
objBL.ConnectionString = "Provider=sqloledb;server=server;database=databaseName;integrated security=SSPI";
objBL.ErrorLogFile = "error.xml";
objBL.KeepIdentity = false;
objBL.Execute ("schema.xml","data.xml");


My xml looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<foo>
<row>
<BICYCLE_NUM>624</BICYCLE_NUM>
<BICYCLE_ORDER>1</BICYCLE_ORDER>
</row>
<row>
<BICYCLE_NUM>623</BICYCLE_NUM>
<BICYCLE_ORDER>2</BICYCLE_ORDER>
</row>
<row>
<BICYCLE_NUM>681</BICYCLE_NUM>
<BICYCLE_ORDER>3</BICYCLE_ORDER>
</row>
<row>
<BICYCLE_NUM>625</BICYCLE_NUM>
<BICYCLE_ORDER>4</BICYCLE_ORDER>
</row>
<row>
<BICYCLE_NUM>680</BICYCLE_NUM>
<BICYCLE_ORDER>5</BICYCLE_ORDER>
</row>
</foo>


And XSD looks like this:


<xsd:schema xmlns:xsd="XML Schema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="row" sql:relation="BICYCLE"
sql:key-fields="BICYCLE_NUM">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="BICYCLE_NUM" type="xsd:integer" />
<xsd:element name="BICYCLE_ORDER" type="xsd:integer" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>

</xsd:schema>


SQL table looks like this:


CREATE TABLE BICYCLE (
BICYCLE_NUM INT ,
BICYCLE_ORDER INT
GO

CREATE UNIQUE NONCLUSTERED INDEX IX_BICYCLE_NUM__BICYCLE_ORDER
ON Wagon (BICYCLE_NUM, BICYCLE_ORDER);


I've tried to set CheckConstraints = true, nevertheless the result is the same. The error is thrown:


SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class objBL = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class
{
ConnectionString = connectionString,
ErrorLogFile = "error.xml",
KeepIdentity = true,
CheckConstraints = true
};




My question is how can I prevent insert of duplicates using SQLXMLBULKLOADLib?



Continue reading...