Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do you Bulk Import Duplicate key-fields using SQLXML

Status
Not open for further replies.

Jimdeb03

IS-IT--Management
Dec 10, 2002
89
0
0
US
This was pulled from the SQLXML 3.0 help. I'm using VS2005, SQLExpress and SQLXML 3.0

Code:
<ROOT>
  <Order OrderID="11" CustomerID="ALFKI">
    <Product ProductID="11" ProductName="Chai" />
    <Product ProductID="22" ProductName="Chang" />
  </Order>
  <Order OrderID="22" CustomerID="ANATR">
    <Product ProductID="33" ProductName="Aniseed Syrup" />
    <Product ProductID="44" ProductName="Gumbo Mix" />
  </Order>
  <Order OrderID="23" CustomerID="ANATR">
    <Product ProductID="33" ProductName="Aniseed Syrup" />
    <Product ProductID="44" ProductName="Gumbo Mix" />
  </Order>
</ROOT>

You'll see the same CustomerID has multiple ORDERIDs. The same ProductID is used for multiple OrderID's.
I was able to compensate for the duplicate CustomerID's by editing the Order element...
sql:key-fields="OrderID CustomerID"

Code:
<xsd:schema xmlns:xsd="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema"[/URL]
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:annotation>
    <xsd:appinfo>
      <sql:relationship name="OrderOD"
            parent="Ord"
            parent-key="OrderID"
            child="OrderDetail"
            child-key="OrderID" />

      <sql:relationship name="ODProduct"
            parent="OrderDetail"
            parent-key="ProductID"
            child="Product"
            child-key="ProductID" 
            inverse="true"/>
    </xsd:appinfo>
  </xsd:annotation>

  <xsd:element name="Order" sql:relation="Ord" sql:key-fields="OrderID CustomerID" >
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="Product"
                   sql:relation="Product" 
                   sql:key-fields="ProductID ProductName"
                   sql:relationship="OrderOD ODProduct">
          <xsd:complexType>
            <xsd:attribute name="ProductID" type="xsd:int" />
            <xsd:attribute name="ProductName" type="xsd:string" />
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
      <xsd:attribute name="OrderID"   type="xsd:integer" />
      <xsd:attribute name="CustomerID"   type="xsd:string" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

However, I'm unable to produce the same affect when populating the Product table. Instead I get 'Cannot insert duplicate key in object 'dbo.Product'.

Editing the Product element at sql:key-fields="ProductID ProductName" by adding ProductName doesn't work

How do you edit the schema to allow for duplicate attributes in this situation?
thanks
Jim
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top