How to create a Mapping file for this XML file

<Item>
<UPC></UPC>
<ShortSku>400100084564</ShortSku>
<Vendor>AEGIS</Vendor>
<ItemID>650FORK</ItemID>
<StoreData storenum="1">
<Onhand>1</Onhand>
<AvgCost>85.00</AvgCost>
<CurrRetail>149.95</CurrRetail>
</StoreData>
</Item>
<Item>
<UPC></UPC>
<ShortSku>400100084571</ShortSku>
<Vendor>AEGIS</Vendor>
<ItemID>TRIDENT</ItemID>
<StoreData storenum="1">
<Onhand>0</Onhand>
<AvgCost>0.00</AvgCost>
<CurrRetail>2000.00</CurrRetail>
</StoreData>
</Item>
Would anyone know what the file would be which coulkd map this to two tables
.
Item and Store with filed names identical to the string names in the xml
files.Is there a way to generate the mapping files automatically?
Thanks In anticipation.
AjayHello,
Your xsd schema should look like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:dt="urn:schemas-microsoft-com:datatypes"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="Item_Store" parent="Item" parent-key="ItemID"
child-key="ItemID" child="StoreData"/>
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Item" sql:relation="Item">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="UPC" type="xsd:string"/>
<xsd:element name="ShortSKU" type="xsd:int"/>
<xsd:element name="Vendor" type="xsd:string"/>
<xsd:element name="ItemID" type="xsd:string"/>
<xsd:element name="StoreData" sql:relation="StoreData"
sql:relationship="Item_Store">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Onhand" type="xsd:int"/>
<xsd:element name="AvgCost" type="xsd:decimal"/>
<xsd:element name="CurrRetail" type="xsd:decimal"/>
</xsd:sequence>
<xsd:attribute name="storenum" type="xsd:int"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this helps.
Best regards,
Monica Frintu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm" .