Prevent duplicates when insert by SQLXMLBULKLOADLib

Question

I am wondering if is there a 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="http://www.w3.org/2001/XMLSchema"  
        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>

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

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?

Answer 1

After exploring SQLXMLBulkLoad4Class, IgnoreDuplicateKeys property was found. So we can use IgnoreDuplicateKeys property:

SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class objBL = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class
{
    ConnectionString = connectionString,
    ErrorLogFile = "error.xml",
    ISQLXMLBulkLoad_IgnoreDuplicateKeys = true,
    IgnoreDuplicateKeys = true
};
objBL.Execute(xsdScheme, xmlFileName);

In addition, CONSTRAINT should be used, not UNIQUE NONCLUSTERED INDEX:

CREATE TABLE BICYCLE 
(  
      BICYCLE_NUM   INT
    , BICYCLE_ORDER INT
    , CONSTRAINT PK_BICYCLENUM_BICYCLEORDER PRIMARY KEY 
        NONCLUSTERED (BICYCLE_NUM, BICYCLE_ORDER)    
)
GO