This was pulled from the SQLXML 3.0 help. I'm using VS2005, SQLExpress and SQLXML 3.0
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"
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
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