problem with adding sql:relation

hello,
I'm trying to load xml data using SQLXMLBulkLoad
my xml schema is:
<xsd:element name="OrderDetail"/>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CompanyID"/>
<xsd:element name="CompanyName"/>
<xsd:element name="OrderDate"/>
<xsd:element name="Price"/>
</xsd:sequence>
</xsd:complexType>
example xml:
<OrderDetail>
<CompanyID>123</CompanyID>
<CompanyName>ABC</CompanyName>
<OrderDate>2002-10-10</OrderDate>
<Price>100$</Price>
</OrderDetail>
I've 2 tables which I want to update using this xml file:
Table Company:
===========
CompanyID (primary)
CompanyName
Table Orders:
==========
CompanyID (primary, foreign from Company)
OrderDate
Price
how do I put the "sql:relation" annotation in 'OrderDetail' when its data should update the two tables in fields that are not the primary-key ? (CompanyName --> Company
and
OrderDate,Price --> Orders)
Best Regards
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
This should work:
<xsd:element name="OrderDetail" sql:relation="Orders" />
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CompanyID"/>
<xsd:element name="CompanyName" sql:relation="Company"
sql:relationship="OrdersCompany" />
<xsd:element name="OrderDate"/>
<xsd:element name="Price"/>
</xsd:sequence>
</xsd:complexType>
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="OrdersCompany"
parent="Orders"
parent-key="CompanyID"
child="Company"
child-key="CompanyID" />
</xsd:appinfo>
</xsd:annotation>
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad/
sql