Problem with DataSet schema interpreted from xsd

Question

I have an xsd file which describes a DataSet schema I use to read/write my DataSet to disk as an xml file. I did not write the schema from hand, rather, I wrote the xml file by hand, inferred the schema from the xml file, and then wrote out the xsd schema. (I am pretty new to this...)

Anyway here is the schema (some amazon.com stuff going on here):

<?xml version="1.0" standalone="yes"?>
<xs:schema id="Items" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="Items" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
  <xs:choice minOccurs="0" maxOccurs="unbounded">
    <xs:element name="Item">
      <xs:complexType>
        <xs:sequence>
          <xs:element name="ASIN" type="xs:string" />
          <xs:element name="Title" type="xs:string" />
          <xs:element name="Offer" minOccurs="0" maxOccurs="unbounded">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="MerchantName" type="xs:string" />
                <xs:element name="Price" type="xs:string" />
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:sequence>
      </xs:complexType>
    </xs:element>
  </xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>

When the DataSet reads the schema, it creates two tables linked by an ad-hoc key it creates called Item_Id, which is unique to each Item, and maps to an Offer. So I get one Item table with columns (ASIN, Title, Item_Id) and an Offer table with columns (MerchantName, Price, Item_Id).

The problem here is that the ASIN is already a unique identifier for the item, and so the schema import process has introduced some redundancy and makes the code more awkward than it needs to be. How can I change this schema to end up with 2 tables like so (ASIN, Title) and (ASIN, MerchantName, Price)?

Thanks!

Answer 1

XSD schema automatically creates a random Item_Id for identity unit unless you declared a primary key.

Assigning primary key in your xsd schema is missing.

<xs:unique name="Constraint1" msdata:PrimaryKey="true">
      <xs:selector xpath=".//Item" />            
      <xs:field xpath="ASIN"/>
</xs:unique>

That way,there will be no Item_Id in your dataset and your schema must look like as follows:

<?xml version="1.0" standalone="yes"?>
<xs:schema id="Items" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema"       xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="Items" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
  <xs:choice minOccurs="0" maxOccurs="unbounded">
    <xs:element name="Item">
      <xs:complexType>
        <xs:sequence>
          <xs:element name="ASIN" type="xs:string"  />
          <xs:element name="Title" type="xs:string" />
          <xs:element name="Offer" minOccurs="0" maxOccurs="unbounded">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="MerchantName" type="xs:string" />
                <xs:element name="Price" type="xs:string" />
              </xs:sequence>
            </xs:complexType>
            <xs:unique name="Constraint1" msdata:PrimaryKey="true">
              <xs:selector xpath=".//Item" />            
              <xs:field xpath="ASIN"/>
            </xs:unique>
          </xs:element>
        </xs:sequence>
      </xs:complexType>
    </xs:element>
  </xs:choice>
</xs:complexType>
<xs:unique name="Constraint2" msdata:PrimaryKey="true">
  <xs:selector xpath=".//Item" />
  <xs:field xpath="ASIN" />
</xs:unique>

Best Regards
Myra