Loading ...
Sorry, an error occurred while loading the content.

Re: [xml-dbms] Storing two XML elements in the same table

Expand Messages
  • Ronald Bourret
    Suppose you have an XML document that looks like this: Sam Smith English 123 Main
    Message 1 of 17 , Aug 3, 2005
    • 0 Attachment
      Suppose you have an XML document that looks like this:

      <people>
      <author>
      <name>Sam Smith</name>
      <language>English</language>
      <address>
      <street>123 Main St.</street>
      <city>Anywhere</city>
      <postcode>1234567</postcode>
      </address>
      </author>
      <plumber>
      <name>Joe Jones</name>
      <type>home plumbing</type>
      <address>
      <street>123 Side St.</street>
      <city>Nowhere</city>
      <postcode>7654321</postcode>
      </address>
      </plumber>
      </people>

      Now suppose you want to store author information in the Authors table,
      plumber information in the Plumbers table, and addresses in the Address
      table. The Authors and Plumbers tables are connected to the Addresses
      table by having a primary key in the Addresses table and foreign keys in
      each of the Authors and Plumbers tables.

      (You could also reverse this and have two different foreign keys in the
      Addresses table -- one pointing to the Authors table and one pointing to
      the Plumbers table -- but this would be poor database design.)

      The map document needs class maps for the author, plumber, and address
      elements. In the class maps for author and plumber, there should be
      RelatedClass elements for the address element. That is, there are two
      different RelatedClass elements for the address element -- one inside
      the ClassMap for author and one inside the ClassMap for plumber.

      Make sense?

      -- Ron

      sctt_bainbridge wrote:

      > Hi,
      >
      > I have an XML file that has parts that are common (address details)
      > but they have different parent elements. I want to store them in the
      > same table. The question is what is the best way to link everything
      > together so that it stores and retreives correctly. Before I get stuck
      > into this is there an example you can point me to??
      >
      > I am part way through a complex example but I can run up a simple
      > example if that helps but I imagine that what I want to do is pretty
      > common.
    • sctt_bainbridge
      Hi, I am trying to get an example going with no luck, I think it is because I have the foreign keys in the master tables as you suggested.. ...
      Message 2 of 17 , Aug 7, 2005
      • 0 Attachment
        Hi,

        I am trying to get an example going with no luck, I think it is
        because I have the foreign keys in the master tables as you
        suggested..

        The xml I have is:
        ------------------
        <contact>
        <contactId>1</contactId>
        <firstName></firstName>
        <lastName></lastName>
        <homeDetails>
        <address>
        <addressId></addressId>
        <street></street>
        <city></city>
        </address>
        </homeDetails>
        <workDetails>
        <companyName></companyName>
        <companyType></companyType>
        <incorporated></incorporated>
        <address>
        <addressId></addressId>
        <street></street>
        <city></city>
        </address>
        </workDetails>
        </contact>
        ----------------------
        The only complexity is that the addresses have to be many to one,
        that is many addresses have to be possible for the one set of home
        or work details hence the use of address_id to allow this.

        The database design I am using is:

        --------------------------------

        drop table xform_contact_address;

        commit;

        create table xform_contact_address (
        contact_id integer not null,
        address_id integer not null,
        street varchar(250),
        city varchar(100),
        CONSTRAINT pk_contact_address PRIMARY KEY
        (contact_id,address_id));

        commit;

        drop table xform_contact_work_details;

        commit;

        create table xform_contact_work_details (
        contact_id integer not null,
        company_name varchar(150),
        company_type varchar(50),
        incorporated varchar(1),
        address_id integer not null,
        CONSTRAINT pk_contact_work PRIMARY KEY (contact_id),
        CONSTRAINT fk_contact_work_addr FOREIGN KEY
        (contact_id,address_id)
        REFERENCES xform_contact_address(contact_id,address_id));

        commit;

        drop table xform_contact;

        commit;

        create table xform_contact (
        contact_id integer not null,
        first_name varchar(250),
        last_name varchar(250),
        address_id integer not null,
        CONSTRAINT pk_contact_main PRIMARY KEY (contact_id),
        CONSTRAINT fk_contact_main_addr FOREIGN KEY
        (contact_id,address_id)
        REFERENCES xform_contact_address(contact_id,address_id),
        CONSTRAINT fk_contact_work FOREIGN KEY (contact_id)
        REFERENCES xform_contact_work_details(contact_id));


        commit;



        -------------------------------

        That is the main and work details tables are connected by a
        primary / foreign key on contact_id and both tables are connected to
        the address table by contact_id + address_id fields.

        The map file for this is:

        -----------------------------

        <?xml version='1.0' ?>

        <!DOCTYPE XMLToDBMS SYSTEM "xmldbms2.dtd" >

        <XMLToDBMS Version="2.0"
        xmlns="http://www.xmlmiddleware.org/xmldbms/v2">
        <Options>
        <SimpleDateFormat Pattern="yyyy-MM-dd"
        DefaultForTypes="DATE" />
        </Options>
        <Databases>
        <Database>
        <Catalog>
        <Schema>
        <Table Name="XFORM_CONTACT">
        <Column Name="CONTACT_ID" DataType="INTEGER"
        Nullable="No"/>
        <Column Name="FIRST_NAME" DataType="VARCHAR"
        Length="250" Nullable="Yes"/>
        <Column Name="LAST_NAME" DataType="VARCHAR"
        Length="250" Nullable="Yes"/>
        <Column Name="ADDRESS_ID" DataType="INTEGER"
        Nullable="No"/>
        <PrimaryKey Name="PK_CONTACT_MAIN">
        <UseColumn Name="CONTACT_ID"/>
        </PrimaryKey>
        <ForeignKey Name="FK_CONTACT_MAIN_ADDR">
        <UseTable Name="XFORM_CONTACT_ADDRESS" />
        <UseUniqueKey
        Name="PK_CONTACT_ADDRESS" />
        <UseColumn Name="CONTACT_ID"/>
        <UseColumn Name="ADDRESS_ID"/>
        </ForeignKey>
        </Table>
        <Table Name="XFORM_CONTACT_WORK_DETAILS">
        <Column Name="CONTACT_ID" DataType="INTEGER"
        Nullable="No"/>
        <Column Name="COMPANY_NAME"
        DataType="VARCHAR" Length="150" Nullable="Yes"/>
        <Column Name="COMPANY_TYPE"
        DataType="VARCHAR" Length="50" Nullable="Yes"/>
        <Column Name="INCORPORATED"
        DataType="VARCHAR" Length="1" Nullable="Yes"/>
        <Column Name="ADDRESS_ID" DataType="INTEGER"
        Nullable="No"/>
        <PrimaryKey Name="PK_CONTACT_WORK">
        <UseColumn Name="CONTACT_ID"/>
        </PrimaryKey>
        <ForeignKey Name="FK_CONTACT_WORK">
        <UseTable Name="XFORM_CONTACT" />
        <UseUniqueKey Name="PK_CONTACT_MAIN" />
        <UseColumn Name="CONTACT_ID"/>
        </ForeignKey>
        <ForeignKey Name="FK_CONTACT_WORK_ADDR">
        <UseTable Name="XFORM_CONTACT_ADDRESS" />
        <UseUniqueKey
        Name="PK_CONTACT_ADDRESS" />
        <UseColumn Name="CONTACT_ID"/>
        <UseColumn Name="ADDRESS_ID"/>
        </ForeignKey>
        </Table>
        <Table Name="XFORM_CONTACT_ADDRESS">
        <Column Name="CONTACT_ID" DataType="INTEGER"
        Nullable="No"/>
        <Column Name="ADDRESS_ID" DataType="INTEGER"
        Nullable="No"/>
        <Column Name="STREET" DataType="VARCHAR"
        Length="50" Nullable="Yes"/>
        <Column Name="CITY" DataType="VARCHAR"
        Length="50" Nullable="Yes"/>
        <PrimaryKey Name="PK_CONTACT_ADDRESS">
        <UseColumn Name="CONTACT_ID"/>
        <UseColumn Name="ADDRESS_ID"/>
        </PrimaryKey>
        </Table>
        </Schema>
        </Catalog>
        </Database>
        </Databases>

        <Maps>
        <ClassMap>
        <ElementType Name="contact"/>
        <ToClassTable Name="XFORM_CONTACT"/>
        <PropertyMap>
        <ElementType Name="contactId"/>
        <ToColumn Name="CONTACT_ID"/>
        </PropertyMap>
        <PropertyMap>
        <ElementType Name="firstName"/>
        <ToColumn Name="FIRST_NAME"/>
        </PropertyMap>
        <PropertyMap>
        <ElementType Name="secondName"/>
        <ToColumn Name="LAST_NAME"/>
        </PropertyMap>
        <InlineMap>
        <ElementType Name="homeDetails" />
        <RelatedClass KeyInParentTable="Unique">
        <ElementType Name="address"/>
        <UseForeignKey Name="FK_CONTACT_MAIN_ADDR"/>
        <OrderColumn Name="ADDRESS_ID"
        Direction="Ascending" />
        </RelatedClass>
        </InlineMap>
        <RelatedClass KeyInParentTable="Unique">
        <ElementType Name="workDetails"/>
        <UseForeignKey Name="FK_CONTACT_WORK"/>
        <OrderColumn Name="CONTACT_ID"
        Direction="Ascending" />
        </RelatedClass>
        </ClassMap>
        <ClassMap>
        <ElementType Name="workDetails"/>
        <ToClassTable Name="XFORM_CONTACT_WORK_DETAILS"/>
        <PropertyMap>
        <ElementType Name="companyName"/>
        <ToColumn Name="COMPANY_NAME"/>
        </PropertyMap>
        <PropertyMap>
        <ElementType Name="companyType"/>
        <ToColumn Name="COMPANY_TYPE"/>
        </PropertyMap>
        <PropertyMap>
        <ElementType Name="incorporated"/>
        <ToColumn Name="INCORPORATED"/>
        </PropertyMap>
        <RelatedClass KeyInParentTable="Unique">
        <ElementType Name="address"/>
        <UseForeignKey Name="FK_CONTACT_WORK_ADDR"/>
        <OrderColumn Name="ADDRESS_ID"
        Direction="Ascending" />
        </RelatedClass>
        </ClassMap>
        <ClassMap>
        <ElementType Name="address"/>
        <ToClassTable Name="XFORM_CONTACT_ADDRESS"/>
        <PropertyMap>
        <ElementType Name="addressId"/>
        <ToColumn Name="ADDRESS_ID"/>
        </PropertyMap>
        <PropertyMap>
        <ElementType Name="street"/>
        <ToColumn Name="STREET"/>
        </PropertyMap>
        <PropertyMap>
        <ElementType Name="city"/>
        <ToColumn Name="CITY"/>
        </PropertyMap>
        </ClassMap>
        </Maps>
        </XMLToDBMS>


        -----------------------------

        This combination gives me the following error:

        java.lang.NullPointerException
        at
        org.xmlmiddleware.xmldbms.maps.factories.MapCompiler.resolveRCMWrappe
        r(MapCompiler.java:1900)
        at
        org.xmlmiddleware.xmldbms.maps.factories.MapCompiler.resolveRCMWrappe
        rs(MapCompiler.java:1880)
        at
        org.xmlmiddleware.xmldbms.maps.factories.MapCompiler.endDocument(MapC
        ompiler.java:288)

        ------------------

        I think I have something fundamentally wrong here.... I think I
        should have the foreign keys on the address table but this gets
        complex....

        any ideas???
      • Ronald Bourret
        Hello, There are several problems with your map: 1) You have declared the foreign key linking the XFORM_CONTACT and XFORM_WORK_DETAILS tables incorrectly in
        Message 3 of 17 , Aug 9, 2005
        • 0 Attachment
          Hello,

          There are several problems with your map:

          1) You have declared the foreign key linking the XFORM_CONTACT and
          XFORM_WORK_DETAILS tables incorrectly in your map. In your database
          schema, this foreign key is (correctly) declared in the XFORM_CONTACT
          table. In your map, it is (incorrectly) declared in the
          XFORM_WORK_DETAILS table; move it to the desciption of the XFORM_CONTACT
          table (note changes):

          <ForeignKey Name="FK_CONTACT_WORK">
          <UseTable Name="XFORM_CONTACT_WORK_DETAILS" />
          <UseUniqueKey Name="PK_CONTACT_WORK" />
          <UseColumn Name="CONTACT_ID"/>
          </ForeignKey>

          2) Your RelatedClass elements all omit the UseUniqueKey child. This is
          invalid. (You should always validate your map, action, and filter
          documents during development, since XML-DBMS assumes that they are valid
          and behaves unpredictably if they are not. You can turn off validation
          for production systems if you so choose.)

          3) In all of your RelatedClass elements, the KeyInParentTable attribute
          should be "Foreign". The "parent table" refers to the table to which the
          parent element is mapped. For example, in the relation between the
          contact and homeDetails/address elements, the parent table is the
          XFORM_CONTACT table, which contains a foreign key pointing to the
          address table.

          There may be other problems as well, but I didn't spot anything right away.

          There are also some problems (in my opinion) with your database schema:

          1) The work details table uses the contact ID as its primary key. This
          strikes me as incorrect, since many contacts will use the same company
          information. Instead, the contact_id column should be replaced with a
          company_id column, a company_id columns should be added to the contact
          table, and this column should be used to join the two tables.

          2) Similarly, I don't understand why there is a contact ID in the
          address table. Addresses are independent of contacts. For example, you
          might have two people (husband and wife) who have separate contact IDs
          but the same address.

          -- Ron

          sctt_bainbridge wrote:

          > Hi,
          >
          > I am trying to get an example going with no luck, I think it is
          > because I have the foreign keys in the master tables as you
          > suggested..
          >
          > The xml I have is:
          > ------------------
          > <contact>
          > <contactId>1</contactId>
          > <firstName></firstName>
          > <lastName></lastName>
          > <homeDetails>
          > <address>
          > <addressId></addressId>
          > <street></street>
          > <city></city>
          > </address>
          > </homeDetails>
          > <workDetails>
          > <companyName></companyName>
          > <companyType></companyType>
          > <incorporated></incorporated>
          > <address>
          > <addressId></addressId>
          > <street></street>
          > <city></city>
          > </address>
          > </workDetails>
          > </contact>
          > ----------------------
          > The only complexity is that the addresses have to be many to one,
          > that is many addresses have to be possible for the one set of home
          > or work details hence the use of address_id to allow this.
          >
          > The database design I am using is:
          >
          > --------------------------------
          >
          > drop table xform_contact_address;
          >
          > commit;
          >
          > create table xform_contact_address (
          > contact_id integer not null,
          > address_id integer not null,
          > street varchar(250),
          > city varchar(100),
          > CONSTRAINT pk_contact_address PRIMARY KEY
          > (contact_id,address_id));
          >
          > commit;
          >
          > drop table xform_contact_work_details;
          >
          > commit;
          >
          > create table xform_contact_work_details (
          > contact_id integer not null,
          > company_name varchar(150),
          > company_type varchar(50),
          > incorporated varchar(1),
          > address_id integer not null,
          > CONSTRAINT pk_contact_work PRIMARY KEY (contact_id),
          > CONSTRAINT fk_contact_work_addr FOREIGN KEY
          > (contact_id,address_id)
          > REFERENCES xform_contact_address(contact_id,address_id));
          >
          > commit;
          >
          > drop table xform_contact;
          >
          > commit;
          >
          > create table xform_contact (
          > contact_id integer not null,
          > first_name varchar(250),
          > last_name varchar(250),
          > address_id integer not null,
          > CONSTRAINT pk_contact_main PRIMARY KEY (contact_id),
          > CONSTRAINT fk_contact_main_addr FOREIGN KEY
          > (contact_id,address_id)
          > REFERENCES xform_contact_address(contact_id,address_id),
          > CONSTRAINT fk_contact_work FOREIGN KEY (contact_id)
          > REFERENCES xform_contact_work_details(contact_id));
          >
          >
          > commit;
          >
          >
          >
          > -------------------------------
          >
          > That is the main and work details tables are connected by a
          > primary / foreign key on contact_id and both tables are connected to
          > the address table by contact_id + address_id fields.
          >
          > The map file for this is:
          >
          > -----------------------------
          >
          > <?xml version='1.0' ?>
          >
          > <!DOCTYPE XMLToDBMS SYSTEM "xmldbms2.dtd" >
          >
          > <XMLToDBMS Version="2.0"
          > xmlns="http://www.xmlmiddleware.org/xmldbms/v2">
          > <Options>
          > <SimpleDateFormat Pattern="yyyy-MM-dd"
          > DefaultForTypes="DATE" />
          > </Options>
          > <Databases>
          > <Database>
          > <Catalog>
          > <Schema>
          > <Table Name="XFORM_CONTACT">
          > <Column Name="CONTACT_ID" DataType="INTEGER"
          > Nullable="No"/>
          > <Column Name="FIRST_NAME" DataType="VARCHAR"
          > Length="250" Nullable="Yes"/>
          > <Column Name="LAST_NAME" DataType="VARCHAR"
          > Length="250" Nullable="Yes"/>
          > <Column Name="ADDRESS_ID" DataType="INTEGER"
          > Nullable="No"/>
          > <PrimaryKey Name="PK_CONTACT_MAIN">
          > <UseColumn Name="CONTACT_ID"/>
          > </PrimaryKey>
          > <ForeignKey Name="FK_CONTACT_MAIN_ADDR">
          > <UseTable Name="XFORM_CONTACT_ADDRESS" />
          > <UseUniqueKey
          > Name="PK_CONTACT_ADDRESS" />
          > <UseColumn Name="CONTACT_ID"/>
          > <UseColumn Name="ADDRESS_ID"/>
          > </ForeignKey>
          > </Table>
          > <Table Name="XFORM_CONTACT_WORK_DETAILS">
          > <Column Name="CONTACT_ID" DataType="INTEGER"
          > Nullable="No"/>
          > <Column Name="COMPANY_NAME"
          > DataType="VARCHAR" Length="150" Nullable="Yes"/>
          > <Column Name="COMPANY_TYPE"
          > DataType="VARCHAR" Length="50" Nullable="Yes"/>
          > <Column Name="INCORPORATED"
          > DataType="VARCHAR" Length="1" Nullable="Yes"/>
          > <Column Name="ADDRESS_ID" DataType="INTEGER"
          > Nullable="No"/>
          > <PrimaryKey Name="PK_CONTACT_WORK">
          > <UseColumn Name="CONTACT_ID"/>
          > </PrimaryKey>
          > <ForeignKey Name="FK_CONTACT_WORK">
          > <UseTable Name="XFORM_CONTACT" />
          > <UseUniqueKey Name="PK_CONTACT_MAIN" />
          > <UseColumn Name="CONTACT_ID"/>
          > </ForeignKey>
          > <ForeignKey Name="FK_CONTACT_WORK_ADDR">
          > <UseTable Name="XFORM_CONTACT_ADDRESS" />
          > <UseUniqueKey
          > Name="PK_CONTACT_ADDRESS" />
          > <UseColumn Name="CONTACT_ID"/>
          > <UseColumn Name="ADDRESS_ID"/>
          > </ForeignKey>
          > </Table>
          > <Table Name="XFORM_CONTACT_ADDRESS">
          > <Column Name="CONTACT_ID" DataType="INTEGER"
          > Nullable="No"/>
          > <Column Name="ADDRESS_ID" DataType="INTEGER"
          > Nullable="No"/>
          > <Column Name="STREET" DataType="VARCHAR"
          > Length="50" Nullable="Yes"/>
          > <Column Name="CITY" DataType="VARCHAR"
          > Length="50" Nullable="Yes"/>
          > <PrimaryKey Name="PK_CONTACT_ADDRESS">
          > <UseColumn Name="CONTACT_ID"/>
          > <UseColumn Name="ADDRESS_ID"/>
          > </PrimaryKey>
          > </Table>
          > </Schema>
          > </Catalog>
          > </Database>
          > </Databases>
          >
          > <Maps>
          > <ClassMap>
          > <ElementType Name="contact"/>
          > <ToClassTable Name="XFORM_CONTACT"/>
          > <PropertyMap>
          > <ElementType Name="contactId"/>
          > <ToColumn Name="CONTACT_ID"/>
          > </PropertyMap>
          > <PropertyMap>
          > <ElementType Name="firstName"/>
          > <ToColumn Name="FIRST_NAME"/>
          > </PropertyMap>
          > <PropertyMap>
          > <ElementType Name="secondName"/>
          > <ToColumn Name="LAST_NAME"/>
          > </PropertyMap>
          > <InlineMap>
          > <ElementType Name="homeDetails" />
          > <RelatedClass KeyInParentTable="Unique">
          > <ElementType Name="address"/>
          > <UseForeignKey Name="FK_CONTACT_MAIN_ADDR"/>
          > <OrderColumn Name="ADDRESS_ID"
          > Direction="Ascending" />
          > </RelatedClass>
          > </InlineMap>
          > <RelatedClass KeyInParentTable="Unique">
          > <ElementType Name="workDetails"/>
          > <UseForeignKey Name="FK_CONTACT_WORK"/>
          > <OrderColumn Name="CONTACT_ID"
          > Direction="Ascending" />
          > </RelatedClass>
          > </ClassMap>
          > <ClassMap>
          > <ElementType Name="workDetails"/>
          > <ToClassTable Name="XFORM_CONTACT_WORK_DETAILS"/>
          > <PropertyMap>
          > <ElementType Name="companyName"/>
          > <ToColumn Name="COMPANY_NAME"/>
          > </PropertyMap>
          > <PropertyMap>
          > <ElementType Name="companyType"/>
          > <ToColumn Name="COMPANY_TYPE"/>
          > </PropertyMap>
          > <PropertyMap>
          > <ElementType Name="incorporated"/>
          > <ToColumn Name="INCORPORATED"/>
          > </PropertyMap>
          > <RelatedClass KeyInParentTable="Unique">
          > <ElementType Name="address"/>
          > <UseForeignKey Name="FK_CONTACT_WORK_ADDR"/>
          > <OrderColumn Name="ADDRESS_ID"
          > Direction="Ascending" />
          > </RelatedClass>
          > </ClassMap>
          > <ClassMap>
          > <ElementType Name="address"/>
          > <ToClassTable Name="XFORM_CONTACT_ADDRESS"/>
          > <PropertyMap>
          > <ElementType Name="addressId"/>
          > <ToColumn Name="ADDRESS_ID"/>
          > </PropertyMap>
          > <PropertyMap>
          > <ElementType Name="street"/>
          > <ToColumn Name="STREET"/>
          > </PropertyMap>
          > <PropertyMap>
          > <ElementType Name="city"/>
          > <ToColumn Name="CITY"/>
          > </PropertyMap>
          > </ClassMap>
          > </Maps>
          > </XMLToDBMS>
          >
          >
          > -----------------------------
          >
          > This combination gives me the following error:
          >
          > java.lang.NullPointerException
          > at
          > org.xmlmiddleware.xmldbms.maps.factories.MapCompiler.resolveRCMWrappe
          > r(MapCompiler.java:1900)
          > at
          > org.xmlmiddleware.xmldbms.maps.factories.MapCompiler.resolveRCMWrappe
          > rs(MapCompiler.java:1880)
          > at
          > org.xmlmiddleware.xmldbms.maps.factories.MapCompiler.endDocument(MapC
          > ompiler.java:288)
          >
          > ------------------
          >
          > I think I have something fundamentally wrong here.... I think I
          > should have the foreign keys on the address table but this gets
          > complex....
          >
          > any ideas???
        • sctt_bainbridge
          Hi, I have (hopefully) fixed everything and it works for one address for each related object but if I try for more than one only one gets stored in the
          Message 4 of 17 , Aug 11, 2005
          • 0 Attachment
            Hi,

            I have (hopefully) fixed everything and it works for one address for
            each related object but if I try for more than one only one gets
            stored in the database.

            The XML I am trying to store is:
            ----------------------------
            <?xml version="1.0" encoding="ISO-8859-1" ?>
            <contact>
            <contactId>11</contactId>
            <firstName>Scott</firstName>
            <lastName>Bainbridge</lastName>
            <addressId>3</addressId>
            <address>
            <addressLinkId>3</addressLinkId>
            <contactId>11</contactId>
            <addressId>1</addressId>
            <street>123 My Street</street>
            <city>My Town</city>
            </address>
            <address>
            <addressLinkId>3</addressLinkId>
            <contactId>11</contactId>
            <addressId>2</addressId>
            <street>456 Another Street</street>
            <city>My Town</city>
            </address>
            <workDetails>
            <contactId>11</contactId>
            <companyName>This Company</companyName>
            <companyType>Local</companyType>
            <incorporated>N</incorporated>
            <addressLinkId>4</addressLinkId>
            <address>
            <addressLinkId>4</addressLinkId>
            <contactId>11</contactId>
            <addressId>1</addressId>
            <street>123 work street</street>
            <city>worksville</city>
            </address>
            <address>
            <addressLinkId>4</addressLinkId>
            <contactId>11</contactId>
            <addressId>2</addressId>
            <street>456 work street</street>
            <city>aotherCity</city>
            </address>
            </workDetails>
            </contact>

            ----------------------------

            That is the Contact element has two addresses asscoaited with it,
            the work details which has a one to one relationship with the
            contact element also has two addresses associated with it.

            The database design is:
            ---------------------------
            drop table xform_contact_address;

            commit;

            create table xform_contact_address (
            contact_id integer not null,
            address_link_id integer not null,
            address_id integer not null,
            street varchar(250),
            city varchar(100),
            CONSTRAINT pk_contact_address PRIMARY KEY
            (contact_id,address_link_id));

            commit;

            drop table xform_contact_work_details;

            commit;

            create table xform_contact_work_details (
            contact_id integer not null,
            company_name varchar(150),
            company_type varchar(50),
            incorporated varchar(1),
            address_link_id integer not null,
            CONSTRAINT pk_contact_work PRIMARY KEY (contact_id),
            CONSTRAINT fk_contact_work_address FOREIGN KEY
            (contact_id,address_link_id)
            REFERENCES xform_contact_address(contact_id,address_link_id));


            commit;

            drop table xform_contact;

            commit;

            create table xform_contact (
            contact_id integer not null,
            first_name varchar(250),
            last_name varchar(250),
            address_link_id integer not null,
            CONSTRAINT pk_contact PRIMARY KEY (contact_id),
            CONSTRAINT uk_contact UNIQUE (contact_id,address_link_id),
            CONSTRAINT fk_contact_work FOREIGN KEY (contact_id)
            REFERENCES xform_contact_work_details(contact_id),
            CONSTRAINT fk_contact_address FOREIGN KEY
            (contact_id,address_link_id)
            REFERENCES xform_contact_address(contact_id,address_link_id));

            commit;

            ---------------------------

            and the map file is:

            ---------------------------
            <?xml version='1.0' ?>

            <!DOCTYPE XMLToDBMS SYSTEM "xmldbms2.dtd" >

            <XMLToDBMS Version="2.0"
            xmlns="http://www.xmlmiddleware.org/xmldbms/v2">
            <Options>
            <SimpleDateFormat Pattern="yyyy-MM-dd"
            DefaultForTypes="DATE" />
            </Options>
            <Databases>
            <Database>
            <Catalog>
            <Schema>
            <Table Name="XFORM_CONTACT_WORK_DETAILS">
            <Column Name="CONTACT_ID" DataType="INTEGER"
            Nullable="No"/>
            <Column Name="COMPANY_NAME"
            DataType="VARCHAR" Length="150" Nullable="Yes"/>
            <Column Name="COMPANY_TYPE"
            DataType="VARCHAR" Length="50" Nullable="Yes"/>
            <Column Name="INCORPORATED"
            DataType="VARCHAR" Length="1" Nullable="Yes"/>
            <Column Name="ADDRESS_LINK_ID"
            DataType="INTEGER" Nullable="No"/>
            <PrimaryKey Name="PK_CONTACT_WORK">
            <UseColumn Name="CONTACT_ID"
            KeyGenerator="Database"/>
            </PrimaryKey>
            <UniqueKey Name="UK_CONTACT_WORK">
            <UseColumn Name="CONTACT_ID"/>
            <UseColumn Name="ADDRESS_LINK_ID"/>
            </UniqueKey>
            <ForeignKey Name="FK_CONTACT_WORK_ADDRESS">
            <UseTable Name="XFORM_CONTACT_ADDRESS" />
            <UseUniqueKey Name="PK_CONTACT_ADDRESS" />
            <UseColumn Name="CONTACT_ID"/>
            <UseColumn Name="ADDRESS_LINK_ID"/>
            </ForeignKey>
            </Table>
            <Table Name="XFORM_CONTACT">
            <Column Name="CONTACT_ID" DataType="INTEGER"
            Nullable="No"/>
            <Column Name="FIRST_NAME" DataType="VARCHAR"
            Length="250" Nullable="Yes"/>
            <Column Name="LAST_NAME" DataType="VARCHAR"
            Length="250" Nullable="Yes"/>
            <Column Name="ADDRESS_LINK_ID"
            DataType="INTEGER" Nullable="No"/>
            <PrimaryKey Name="PK_CONTACT">
            <UseColumn Name="CONTACT_ID"/>
            </PrimaryKey>
            <UniqueKey Name="UK_CONTACT">
            <UseColumn Name="CONTACT_ID"/>
            <UseColumn Name="ADDRESS_LINK_ID"/>
            </UniqueKey>
            <ForeignKey Name="FK_CONTACT_WORK">
            <UseTable
            Name="XFORM_CONTACT_WORK_DETAILS" />
            <UseUniqueKey Name="PK_CONTACT_WORK" />
            <UseColumn Name="CONTACT_ID"/>
            </ForeignKey>
            <ForeignKey Name="FK_CONTACT_ADDRESS">
            <UseTable Name="XFORM_CONTACT_ADDRESS" />
            <UseUniqueKey Name="PK_CONTACT_ADDRESS" />
            <UseColumn Name="CONTACT_ID"/>
            <UseColumn Name="ADDRESS_LINK_ID"/>
            </ForeignKey>
            </Table>
            <Table Name="XFORM_CONTACT_ADDRESS">
            <Column Name="CONTACT_ID" DataType="INTEGER"
            Nullable="No"/>
            <Column Name="ADDRESS_LINK_ID"
            DataType="INTEGER" Nullable="No"/>
            <Column Name="STREET" DataType="VARCHAR"
            Length="50" Nullable="Yes"/>
            <Column Name="CITY" DataType="VARCHAR"
            Length="50" Nullable="Yes"/>
            <Column Name="ADDRESS_ID" DataType="INTEGER"
            Nullable="No"/>
            <PrimaryKey Name="PK_CONTACT_ADDRESS">
            <UseColumn Name="CONTACT_ID"
            KeyGenerator="Database"/>
            <UseColumn Name="ADDRESS_LINK_ID"
            KeyGenerator="Database"/>
            </PrimaryKey>
            </Table>
            </Schema>
            </Catalog>
            </Database>
            </Databases>
            <Maps>
            <ClassMap>
            <ElementType Name="workDetails"/>
            <ToClassTable Name="XFORM_CONTACT_WORK_DETAILS"/>
            <PropertyMap>
            <ElementType Name="contactId"/>
            <ToColumn Name="CONTACT_ID"/>
            </PropertyMap>
            <PropertyMap>
            <ElementType Name="companyName"/>
            <ToColumn Name="COMPANY_NAME"/>
            </PropertyMap>
            <PropertyMap>
            <ElementType Name="companyType"/>
            <ToColumn Name="COMPANY_TYPE"/>
            </PropertyMap>
            <PropertyMap>
            <ElementType Name="incorporated"/>
            <ToColumn Name="INCORPORATED"/>
            </PropertyMap>
            <PropertyMap>
            <ElementType Name="addressLinkId"/>
            <ToColumn Name="ADDRESS_LINK_ID"/>
            </PropertyMap>
            <RelatedClass KeyInParentTable="Foreign">
            <ElementType Name="address"/>
            <UseUniqueKey Name="PK_CONTACT_ADDRESS"/>
            <UseForeignKey Name="FK_CONTACT_WORK_ADDRESS"/>
            </RelatedClass>
            </ClassMap>
            <ClassMap>
            <ElementType Name="contact"/>
            <ToClassTable Name="XFORM_CONTACT"/>
            <PropertyMap>
            <ElementType Name="contactId"/>
            <ToColumn Name="CONTACT_ID"/>
            </PropertyMap>
            <PropertyMap>
            <ElementType Name="firstName"/>
            <ToColumn Name="FIRST_NAME"/>
            </PropertyMap>
            <PropertyMap>
            <ElementType Name="lastName"/>
            <ToColumn Name="LAST_NAME"/>
            </PropertyMap>
            <PropertyMap>
            <ElementType Name="addressLinkId"/>
            <ToColumn Name="ADDRESS_LINK_ID"/>
            </PropertyMap>
            <RelatedClass KeyInParentTable="Foreign">
            <ElementType Name="workDetails"/>
            <UseUniqueKey Name="PK_CONTACT_WORK"/>
            <UseForeignKey Name="FK_CONTACT_WORK"/>
            </RelatedClass>
            <RelatedClass KeyInParentTable="Foreign">
            <ElementType Name="address"/>
            <UseUniqueKey Name="PK_CONTACT_ADDRESS"/>
            <UseForeignKey Name="FK_CONTACT_ADDRESS"/>
            </RelatedClass>
            </ClassMap>
            <ClassMap>
            <ElementType Name="address"/>
            <ToClassTable Name="XFORM_CONTACT_ADDRESS"/>
            <PropertyMap>
            <ElementType Name="addressLinkId"/>
            <ToColumn Name="ADDRESS_LINK_ID"/>
            </PropertyMap>
            <PropertyMap>
            <ElementType Name="addressId"/>
            <ToColumn Name="ADDRESS_ID"/>
            </PropertyMap>
            <PropertyMap>
            <ElementType Name="contactId"/>
            <ToColumn Name="CONTACT_ID"/>
            </PropertyMap>
            <PropertyMap>
            <ElementType Name="street"/>
            <ToColumn Name="STREET"/>
            </PropertyMap>
            <PropertyMap>
            <ElementType Name="city"/>
            <ToColumn Name="CITY"/>
            </PropertyMap>
            </ClassMap>
            </Maps>
            </XMLToDBMS>


            ---------------------------

            The contact and work details are joined by the contact_id field via
            the foreign index which is fine, this is only one to one so that is
            easy. The contact to address is many (addresses) to one, it uses the
            contact_id to link it to the contact and the address_link_id to
            identifiy the addresses as belonging to the contact object and not
            the work details object. There is also an address_id field in the
            address table to order these.

            The work details and address tables are joined by the contact_id and
            address_link_id fields as per the contact element but with a
            different value for the address_link_id to correctly associate the
            values in the address table with teh correct parent.

            So it works but only saves one address for each parent, that is the
            last one in the XML file. I have been able to get it to do one to
            many but with only one parent table, not two.

            I feel that I am really pushing your patience but I have a large
            project that I am using your program for and need to work this stuff
            out. The real project has around 14 tables and is reasonably complex
            so it is talking me a bit to get my head around it all. I am very
            grateful for all the help you have given me.

            Thanks,


            Scott.
          • Jabir Ahmed
            Hello I am new to this XML-Dbms can u sugest a solution on how to transfer records into a mysql-table where i have a xml like
            Message 5 of 17 , Aug 12, 2005
            • 0 Attachment
              Hello
              I am new to this XML-Dbms
              can u sugest a solution on how to transfer records
              into a mysql-table where i have a xml like

              <details>
              <contact name="xyz" age="21"></contact>
              <contact name="ABC" age="31"></contact>
              </details>

              Here the problem is the details come as attributes and
              not as elements.

              Regards,

              Jabir

              --- sctt_bainbridge <s.bainbridge@...> wrote:

              > Hi,
              >
              > I have (hopefully) fixed everything and it works for
              > one address for
              > each related object but if I try for more than one
              > only one gets
              > stored in the database.
              >
              > The XML I am trying to store is:
              > ----------------------------
              > <?xml version="1.0" encoding="ISO-8859-1" ?>
              > <contact>
              > <contactId>11</contactId>
              > <firstName>Scott</firstName>
              > <lastName>Bainbridge</lastName>
              > <addressId>3</addressId>
              > <address>
              > <addressLinkId>3</addressLinkId>
              > <contactId>11</contactId>
              > <addressId>1</addressId>
              > <street>123 My Street</street>
              > <city>My Town</city>
              > </address>
              > <address>
              > <addressLinkId>3</addressLinkId>
              > <contactId>11</contactId>
              > <addressId>2</addressId>
              > <street>456 Another Street</street>
              > <city>My Town</city>
              > </address>
              > <workDetails>
              > <contactId>11</contactId>
              > <companyName>This Company</companyName>
              > <companyType>Local</companyType>
              > <incorporated>N</incorporated>
              > <addressLinkId>4</addressLinkId>
              > <address>
              > <addressLinkId>4</addressLinkId>
              > <contactId>11</contactId>
              > <addressId>1</addressId>
              > <street>123 work street</street>
              > <city>worksville</city>
              > </address>
              > <address>
              > <addressLinkId>4</addressLinkId>
              > <contactId>11</contactId>
              > <addressId>2</addressId>
              > <street>456 work street</street>
              > <city>aotherCity</city>
              > </address>
              > </workDetails>
              > </contact>
              >
              > ----------------------------
              >
              > That is the Contact element has two addresses
              > asscoaited with it,
              > the work details which has a one to one relationship
              > with the
              > contact element also has two addresses associated
              > with it.
              >
              > The database design is:
              > ---------------------------
              > drop table xform_contact_address;
              >
              > commit;
              >
              > create table xform_contact_address (
              > contact_id integer not null,
              > address_link_id integer not null,
              > address_id integer not null,
              > street varchar(250),
              > city varchar(100),
              > CONSTRAINT pk_contact_address PRIMARY KEY
              > (contact_id,address_link_id));
              >
              > commit;
              >
              > drop table xform_contact_work_details;
              >
              > commit;
              >
              > create table xform_contact_work_details (
              > contact_id integer not null,
              > company_name varchar(150),
              > company_type varchar(50),
              > incorporated varchar(1),
              > address_link_id integer not null,
              > CONSTRAINT pk_contact_work PRIMARY KEY
              > (contact_id),
              > CONSTRAINT fk_contact_work_address FOREIGN KEY
              > (contact_id,address_link_id)
              > REFERENCES
              > xform_contact_address(contact_id,address_link_id));
              >
              >
              > commit;
              >
              > drop table xform_contact;
              >
              > commit;
              >
              > create table xform_contact (
              > contact_id integer not null,
              > first_name varchar(250),
              > last_name varchar(250),
              > address_link_id integer not null,
              > CONSTRAINT pk_contact PRIMARY KEY (contact_id),
              > CONSTRAINT uk_contact UNIQUE
              > (contact_id,address_link_id),
              > CONSTRAINT fk_contact_work FOREIGN KEY
              > (contact_id)
              > REFERENCES
              > xform_contact_work_details(contact_id),
              > CONSTRAINT fk_contact_address FOREIGN KEY
              > (contact_id,address_link_id)
              > REFERENCES
              > xform_contact_address(contact_id,address_link_id));
              >
              > commit;
              >
              > ---------------------------
              >
              > and the map file is:
              >
              > ---------------------------
              > <?xml version='1.0' ?>
              >
              > <!DOCTYPE XMLToDBMS SYSTEM "xmldbms2.dtd" >
              >
              > <XMLToDBMS Version="2.0"
              > xmlns="http://www.xmlmiddleware.org/xmldbms/v2">
              > <Options>
              > <SimpleDateFormat Pattern="yyyy-MM-dd"
              > DefaultForTypes="DATE" />
              > </Options>
              > <Databases>
              > <Database>
              > <Catalog>
              > <Schema>
              > <Table
              > Name="XFORM_CONTACT_WORK_DETAILS">
              > <Column Name="CONTACT_ID"
              > DataType="INTEGER"
              > Nullable="No"/>
              > <Column Name="COMPANY_NAME"
              > DataType="VARCHAR" Length="150" Nullable="Yes"/>
              > <Column Name="COMPANY_TYPE"
              > DataType="VARCHAR" Length="50" Nullable="Yes"/>
              > <Column Name="INCORPORATED"
              > DataType="VARCHAR" Length="1" Nullable="Yes"/>
              > <Column
              > Name="ADDRESS_LINK_ID"
              > DataType="INTEGER" Nullable="No"/>
              > <PrimaryKey
              > Name="PK_CONTACT_WORK">
              > <UseColumn
              > Name="CONTACT_ID"
              > KeyGenerator="Database"/>
              > </PrimaryKey>
              > <UniqueKey
              > Name="UK_CONTACT_WORK">
              > <UseColumn
              > Name="CONTACT_ID"/>
              > <UseColumn
              > Name="ADDRESS_LINK_ID"/>
              > </UniqueKey>
              > <ForeignKey
              > Name="FK_CONTACT_WORK_ADDRESS">
              > <UseTable
              > Name="XFORM_CONTACT_ADDRESS" />
              > <UseUniqueKey
              > Name="PK_CONTACT_ADDRESS" />
              > <UseColumn
              > Name="CONTACT_ID"/>
              > <UseColumn
              > Name="ADDRESS_LINK_ID"/>
              > </ForeignKey>
              > </Table>
              > <Table Name="XFORM_CONTACT">
              > <Column Name="CONTACT_ID"
              > DataType="INTEGER"
              > Nullable="No"/>
              > <Column Name="FIRST_NAME"
              > DataType="VARCHAR"
              > Length="250" Nullable="Yes"/>
              > <Column Name="LAST_NAME"
              > DataType="VARCHAR"
              > Length="250" Nullable="Yes"/>
              > <Column
              > Name="ADDRESS_LINK_ID"
              > DataType="INTEGER" Nullable="No"/>
              > <PrimaryKey
              > Name="PK_CONTACT">
              > <UseColumn
              > Name="CONTACT_ID"/>
              > </PrimaryKey>
              > <UniqueKey
              > Name="UK_CONTACT">
              > <UseColumn
              > Name="CONTACT_ID"/>
              >
              === message truncated ===


              I do the diffcult immediately,but the impossible take's a little longer!!!

              Cresent canaopy apartments
              #19 Davis Road,
              Thomas town,
              Bangalore -84
              Phone (mobile) : +91-98867 01148
              E-Mail: jabirahmed@...,jabirahmed@...


              __________________________________________________
              Do You Yahoo!?
              Tired of spam? Yahoo! Mail has the best spam protection around
              http://mail.yahoo.com
            • Hiral Shah
              Hi everyone, I am a student working towards my masters project in XML. i was wondering if there was any open source tool with features like stylus studio s
              Message 6 of 17 , Aug 12, 2005
              • 0 Attachment
                Hi everyone,
                I am a student working towards my masters project in
                XML. i was wondering if there was any open source tool
                with features like stylus studio's mapping facility in
                the market.
                Thank you in advance,
                Hshah







                __________________________________
                Yahoo! Mail for Mobile
                Take Yahoo! Mail with you! Check email on your mobile phone.
                http://mobile.yahoo.com/learn/mail
              • Ronald Bourret
                In your map document, use the PropertyMap element with an Attribute child to map attributes. For example (this shows the 2.0 syntax):
                Message 7 of 17 , Aug 13, 2005
                • 0 Attachment
                  In your map document, use the PropertyMap element with an Attribute
                  child to map attributes. For example (this shows the 2.0 syntax):

                  <ClassMap>
                  <ElementType Name="Part"/>
                  <ToClassTable Name="Parts"/>
                  <PropertyMap>
                  <Attribute Name="PartNumber"/>
                  <ToColumn Name="Number"/>
                  </PropertyMap>
                  ...
                  </ClassMap>

                  For more samples, see the *.map files in the samples directory.

                  If you are using version 1.x, you should start by reading the readme.htm
                  document.

                  If you are using version 2.0 alpha 3 (recommended), you should start by
                  reading the version 1.x readme (available online at [1]), then read the
                  version 2.0 readme, which is in the installation directory. While there
                  are some differences in the 1.x and 2.0 mapping languages, the basic
                  ideas are the same. You can also find a lot of information in the DTDs.

                  -- Ron

                  [1] http://www.rpbourret.com/xmldbms/readme.htm

                  Jabir Ahmed wrote:

                  > Hello
                  > I am new to this XML-Dbms
                  > can u sugest a solution on how to transfer records
                  > into a mysql-table where i have a xml like
                  >
                  > <details>
                  > <contact name="xyz" age="21"></contact>
                  > <contact name="ABC" age="31"></contact>
                  > </details>
                  >
                  > Here the problem is the details come as attributes and
                  > not as elements.
                  >
                  > Regards,
                  >
                  > Jabir
                • Ronald Bourret
                  Hello, The problem is that both addresses have the same unique key (address ID = 3 and contact ID = 11) and that (I assume) the action is set to
                  Message 8 of 17 , Aug 14, 2005
                  • 0 Attachment
                    Hello,

                    The problem is that both addresses have the same unique key (address ID
                    = 3 and contact ID = 11) and that (I assume) the action is set to
                    update-or-insert. The first address is inserted. When the second address
                    is encountered, XML-DBMS first checks to see if an address with that key
                    exists. Since it does, it updates the record rather than adding a new
                    record with the same key.

                    But you have several other problems:

                    1) The relationship between the contact and address tables is
                    many-to-many. That is, one contact can have many addresses and one
                    address can be shared by many contacts.

                    This is modeled in relational databases with a join table. For example:

                    create table contacts_to_addresses (
                    contact_id integer not null,
                    address_id integer not null
                    CONSTRAINT pk_contacts_to_addresses PRIMARY KEY
                    (contact_id,address_link_id)),
                    CONSTRAINT fk_contact_id FOREIGN KEY
                    (contact_id)
                    REFERENCES xform_contact(contact_id),
                    CONSTRAINT fk_address_id FOREIGN KEY
                    (address_id)
                    REFERENCES xform_contact_address(address_id));

                    Basically, the join table has two columns, a contact ID and an address
                    ID, both of which are foreign keys pointing back to the contact and
                    address tables, respectively.

                    Many-to-many tables are modeled in XML-DBMS through "wrapper" elements.
                    (Note that this is not the same as the wrapper elements found in filter
                    documents, but the more common usage of the term in the XML community,
                    in which an element is used to wrap a number of elements of the same type.)

                    For example, you could use an addresses element in your XML document:

                    <contact>
                    <contactId>11</contactId>
                    <firstName>Scott</firstName>
                    <lastName>Bainbridge</lastName>
                    <addresses>
                    <address>
                    <addressLinkId>3</addressLinkId>
                    <addressId>1</addressId>
                    <street>123 My Street</street>
                    <city>My Town</city>
                    </address>
                    <address>
                    <addressLinkId>3</addressLinkId>
                    <addressId>2</addressId>
                    <street>456 Another Street</street>
                    <city>My Town</city>
                    </address>
                    </addresses>
                    ...

                    The addresses element would be mapped to the join table. It's ClassMap
                    would not have any PropertyMap children, but would have a RelatedClass
                    element for the address element. Similarly, the ClassMap for the contact
                    element would have a RelatedClass element for the addresses element.

                    Note that the join table has a one-to-many relationship with both the
                    contact and the address table. Thus, the parent key in the contact table
                    / join table relationship is the unique key and the parent key in the
                    join table / address table relationship is the foreign key.

                    2) You need to remove the contact ID column from the address table. This
                    is replaced by the join table.

                    3) There is no reason for the address_link_id column in the address
                    table. The address_id column is sufficient, as it is pointed to by the
                    join table.

                    4) You need to remove the addressId child element of the contact
                    element. This is redundant with the addressLinkId child of the address
                    element. XML-DBMS copies the correct values from table to table to
                    perform the linking between tables. For example, suppose you have the
                    following XML representing a one-to-many relationship:

                    <one>
                    <oneId>1</oneId>
                    <oneProperty>foo</oneProperty>
                    <many>
                    <manyID>a</manyID>
                    <manyProperty>bar</manyProperty>
                    </many>
                    <many>
                    <manyID>b</manyID>
                    <manyProperty>baz</manyProperty>
                    </many>
                    </one>

                    Assuming the one and many tables are linked by the columns one.id and
                    many.oneId, XML-DBMS copies the value of one.id to many.oneId. This is
                    why the class map for the join table described above does not have any
                    PropertyMap children. XML-DBMS copies the contact ID from the contact
                    table to the contact_id column in the join table. Similarly, it copies
                    the address ID from the address table to the address_id column in the
                    join table.

                    5) You need to remove the addressLinkId and contactId children of the
                    address element. Neither is needed.

                    6) You need to remove the contactId and addressLinkId children of the
                    workDetails element, as well as wrapping the address children of the
                    workDetails element in an addresses element and mapping it in the class
                    map for workDetails as was done in (1) in the class map for the contact
                    element.

                    Hope this is clear.

                    Don't worry about asking questions. I answer them as I have time. At the
                    moment, you're getting prompt answers. When something more important
                    (for me) comes up, answers can be delayed by two to three weeks :(
                    Please note that I'll be off email for roughly two weeks starting this
                    Wednesday, so you'll either have to figure things out yourself or wait
                    for an answer.

                    -- Ron


                    sctt_bainbridge wrote:
                    > Hi,
                    >
                    > I have (hopefully) fixed everything and it works for one address for
                    > each related object but if I try for more than one only one gets
                    > stored in the database.
                    >
                    > The XML I am trying to store is:
                    > ----------------------------
                    > <?xml version="1.0" encoding="ISO-8859-1" ?>
                    > <contact>
                    > <contactId>11</contactId>
                    > <firstName>Scott</firstName>
                    > <lastName>Bainbridge</lastName>
                    > <addressId>3</addressId>
                    > <address>
                    > <addressLinkId>3</addressLinkId>
                    > <contactId>11</contactId>
                    > <addressId>1</addressId>
                    > <street>123 My Street</street>
                    > <city>My Town</city>
                    > </address>
                    > <address>
                    > <addressLinkId>3</addressLinkId>
                    > <contactId>11</contactId>
                    > <addressId>2</addressId>
                    > <street>456 Another Street</street>
                    > <city>My Town</city>
                    > </address>
                    > <workDetails>
                    > <contactId>11</contactId>
                    > <companyName>This Company</companyName>
                    > <companyType>Local</companyType>
                    > <incorporated>N</incorporated>
                    > <addressLinkId>4</addressLinkId>
                    > <address>
                    > <addressLinkId>4</addressLinkId>
                    > <contactId>11</contactId>
                    > <addressId>1</addressId>
                    > <street>123 work street</street>
                    > <city>worksville</city>
                    > </address>
                    > <address>
                    > <addressLinkId>4</addressLinkId>
                    > <contactId>11</contactId>
                    > <addressId>2</addressId>
                    > <street>456 work street</street>
                    > <city>aotherCity</city>
                    > </address>
                    > </workDetails>
                    > </contact>
                    >
                    > ----------------------------
                    >
                    > That is the Contact element has two addresses asscoaited with it,
                    > the work details which has a one to one relationship with the
                    > contact element also has two addresses associated with it.
                    >
                    > The database design is:
                    > ---------------------------
                    > drop table xform_contact_address;
                    >
                    > commit;
                    >
                    > create table xform_contact_address (
                    > contact_id integer not null,
                    > address_link_id integer not null,
                    > address_id integer not null,
                    > street varchar(250),
                    > city varchar(100),
                    > CONSTRAINT pk_contact_address PRIMARY KEY
                    > (contact_id,address_link_id));
                    >
                    > commit;
                    >
                    > drop table xform_contact_work_details;
                    >
                    > commit;
                    >
                    > create table xform_contact_work_details (
                    > contact_id integer not null,
                    > company_name varchar(150),
                    > company_type varchar(50),
                    > incorporated varchar(1),
                    > address_link_id integer not null,
                    > CONSTRAINT pk_contact_work PRIMARY KEY (contact_id),
                    > CONSTRAINT fk_contact_work_address FOREIGN KEY
                    > (contact_id,address_link_id)
                    > REFERENCES xform_contact_address(contact_id,address_link_id));
                    >
                    >
                    > commit;
                    >
                    > drop table xform_contact;
                    >
                    > commit;
                    >
                    > create table xform_contact (
                    > contact_id integer not null,
                    > first_name varchar(250),
                    > last_name varchar(250),
                    > address_link_id integer not null,
                    > CONSTRAINT pk_contact PRIMARY KEY (contact_id),
                    > CONSTRAINT uk_contact UNIQUE (contact_id,address_link_id),
                    > CONSTRAINT fk_contact_work FOREIGN KEY (contact_id)
                    > REFERENCES xform_contact_work_details(contact_id),
                    > CONSTRAINT fk_contact_address FOREIGN KEY
                    > (contact_id,address_link_id)
                    > REFERENCES xform_contact_address(contact_id,address_link_id));
                    >
                    > commit;
                    >
                    > ---------------------------
                    >
                    > and the map file is:
                    >
                    > ---------------------------
                    > <?xml version='1.0' ?>
                    >
                    > <!DOCTYPE XMLToDBMS SYSTEM "xmldbms2.dtd" >
                    >
                    > <XMLToDBMS Version="2.0"
                    > xmlns="http://www.xmlmiddleware.org/xmldbms/v2">
                    > <Options>
                    > <SimpleDateFormat Pattern="yyyy-MM-dd"
                    > DefaultForTypes="DATE" />
                    > </Options>
                    > <Databases>
                    > <Database>
                    > <Catalog>
                    > <Schema>
                    > <Table Name="XFORM_CONTACT_WORK_DETAILS">
                    > <Column Name="CONTACT_ID" DataType="INTEGER"
                    > Nullable="No"/>
                    > <Column Name="COMPANY_NAME"
                    > DataType="VARCHAR" Length="150" Nullable="Yes"/>
                    > <Column Name="COMPANY_TYPE"
                    > DataType="VARCHAR" Length="50" Nullable="Yes"/>
                    > <Column Name="INCORPORATED"
                    > DataType="VARCHAR" Length="1" Nullable="Yes"/>
                    > <Column Name="ADDRESS_LINK_ID"
                    > DataType="INTEGER" Nullable="No"/>
                    > <PrimaryKey Name="PK_CONTACT_WORK">
                    > <UseColumn Name="CONTACT_ID"
                    > KeyGenerator="Database"/>
                    > </PrimaryKey>
                    > <UniqueKey Name="UK_CONTACT_WORK">
                    > <UseColumn Name="CONTACT_ID"/>
                    > <UseColumn Name="ADDRESS_LINK_ID"/>
                    > </UniqueKey>
                    > <ForeignKey Name="FK_CONTACT_WORK_ADDRESS">
                    > <UseTable Name="XFORM_CONTACT_ADDRESS" />
                    > <UseUniqueKey Name="PK_CONTACT_ADDRESS" />
                    > <UseColumn Name="CONTACT_ID"/>
                    > <UseColumn Name="ADDRESS_LINK_ID"/>
                    > </ForeignKey>
                    > </Table>
                    > <Table Name="XFORM_CONTACT">
                    > <Column Name="CONTACT_ID" DataType="INTEGER"
                    > Nullable="No"/>
                    > <Column Name="FIRST_NAME" DataType="VARCHAR"
                    > Length="250" Nullable="Yes"/>
                    > <Column Name="LAST_NAME" DataType="VARCHAR"
                    > Length="250" Nullable="Yes"/>
                    > <Column Name="ADDRESS_LINK_ID"
                    > DataType="INTEGER" Nullable="No"/>
                    > <PrimaryKey Name="PK_CONTACT">
                    > <UseColumn Name="CONTACT_ID"/>
                    > </PrimaryKey>
                    > <UniqueKey Name="UK_CONTACT">
                    > <UseColumn Name="CONTACT_ID"/>
                    > <UseColumn Name="ADDRESS_LINK_ID"/>
                    > </UniqueKey>
                    > <ForeignKey Name="FK_CONTACT_WORK">
                    > <UseTable
                    > Name="XFORM_CONTACT_WORK_DETAILS" />
                    > <UseUniqueKey Name="PK_CONTACT_WORK" />
                    > <UseColumn Name="CONTACT_ID"/>
                    > </ForeignKey>
                    > <ForeignKey Name="FK_CONTACT_ADDRESS">
                    > <UseTable Name="XFORM_CONTACT_ADDRESS" />
                    > <UseUniqueKey Name="PK_CONTACT_ADDRESS" />
                    > <UseColumn Name="CONTACT_ID"/>
                    > <UseColumn Name="ADDRESS_LINK_ID"/>
                    > </ForeignKey>
                    > </Table>
                    > <Table Name="XFORM_CONTACT_ADDRESS">
                    > <Column Name="CONTACT_ID" DataType="INTEGER"
                    > Nullable="No"/>
                    > <Column Name="ADDRESS_LINK_ID"
                    > DataType="INTEGER" Nullable="No"/>
                    > <Column Name="STREET" DataType="VARCHAR"
                    > Length="50" Nullable="Yes"/>
                    > <Column Name="CITY" DataType="VARCHAR"
                    > Length="50" Nullable="Yes"/>
                    > <Column Name="ADDRESS_ID" DataType="INTEGER"
                    > Nullable="No"/>
                    > <PrimaryKey Name="PK_CONTACT_ADDRESS">
                    > <UseColumn Name="CONTACT_ID"
                    > KeyGenerator="Database"/>
                    > <UseColumn Name="ADDRESS_LINK_ID"
                    > KeyGenerator="Database"/>
                    > </PrimaryKey>
                    > </Table>
                    > </Schema>
                    > </Catalog>
                    > </Database>
                    > </Databases>
                    > <Maps>
                    > <ClassMap>
                    > <ElementType Name="workDetails"/>
                    > <ToClassTable Name="XFORM_CONTACT_WORK_DETAILS"/>
                    > <PropertyMap>
                    > <ElementType Name="contactId"/>
                    > <ToColumn Name="CONTACT_ID"/>
                    > </PropertyMap>
                    > <PropertyMap>
                    > <ElementType Name="companyName"/>
                    > <ToColumn Name="COMPANY_NAME"/>
                    > </PropertyMap>
                    > <PropertyMap>
                    > <ElementType Name="companyType"/>
                    > <ToColumn Name="COMPANY_TYPE"/>
                    > </PropertyMap>
                    > <PropertyMap>
                    > <ElementType Name="incorporated"/>
                    > <ToColumn Name="INCORPORATED"/>
                    > </PropertyMap>
                    > <PropertyMap>
                    > <ElementType Name="addressLinkId"/>
                    > <ToColumn Name="ADDRESS_LINK_ID"/>
                    > </PropertyMap>
                    > <RelatedClass KeyInParentTable="Foreign">
                    > <ElementType Name="address"/>
                    > <UseUniqueKey Name="PK_CONTACT_ADDRESS"/>
                    > <UseForeignKey Name="FK_CONTACT_WORK_ADDRESS"/>
                    > </RelatedClass>
                    > </ClassMap>
                    > <ClassMap>
                    > <ElementType Name="contact"/>
                    > <ToClassTable Name="XFORM_CONTACT"/>
                    > <PropertyMap>
                    > <ElementType Name="contactId"/>
                    > <ToColumn Name="CONTACT_ID"/>
                    > </PropertyMap>
                    > <PropertyMap>
                    > <ElementType Name="firstName"/>
                    > <ToColumn Name="FIRST_NAME"/>
                    > </PropertyMap>
                    > <PropertyMap>
                    > <ElementType Name="lastName"/>
                    > <ToColumn Name="LAST_NAME"/>
                    > </PropertyMap>
                    > <PropertyMap>
                    > <ElementType Name="addressLinkId"/>
                    > <ToColumn Name="ADDRESS_LINK_ID"/>
                    > </PropertyMap>
                    > <RelatedClass KeyInParentTable="Foreign">
                    > <ElementType Name="workDetails"/>
                    > <UseUniqueKey Name="PK_CONTACT_WORK"/>
                    > <UseForeignKey Name="FK_CONTACT_WORK"/>
                    > </RelatedClass>
                    > <RelatedClass KeyInParentTable="Foreign">
                    > <ElementType Name="address"/>
                    > <UseUniqueKey Name="PK_CONTACT_ADDRESS"/>
                    > <UseForeignKey Name="FK_CONTACT_ADDRESS"/>
                    > </RelatedClass>
                    > </ClassMap>
                    > <ClassMap>
                    > <ElementType Name="address"/>
                    > <ToClassTable Name="XFORM_CONTACT_ADDRESS"/>
                    > <PropertyMap>
                    > <ElementType Name="addressLinkId"/>
                    > <ToColumn Name="ADDRESS_LINK_ID"/>
                    > </PropertyMap>
                    > <PropertyMap>
                    > <ElementType Name="addressId"/>
                    > <ToColumn Name="ADDRESS_ID"/>
                    > </PropertyMap>
                    > <PropertyMap>
                    > <ElementType Name="contactId"/>
                    > <ToColumn Name="CONTACT_ID"/>
                    > </PropertyMap>
                    > <PropertyMap>
                    > <ElementType Name="street"/>
                    > <ToColumn Name="STREET"/>
                    > </PropertyMap>
                    > <PropertyMap>
                    > <ElementType Name="city"/>
                    > <ToColumn Name="CITY"/>
                    > </PropertyMap>
                    > </ClassMap>
                    > </Maps>
                    > </XMLToDBMS>
                    >
                    >
                    > ---------------------------
                    >
                    > The contact and work details are joined by the contact_id field via
                    > the foreign index which is fine, this is only one to one so that is
                    > easy. The contact to address is many (addresses) to one, it uses the
                    > contact_id to link it to the contact and the address_link_id to
                    > identifiy the addresses as belonging to the contact object and not
                    > the work details object. There is also an address_id field in the
                    > address table to order these.
                    >
                    > The work details and address tables are joined by the contact_id and
                    > address_link_id fields as per the contact element but with a
                    > different value for the address_link_id to correctly associate the
                    > values in the address table with teh correct parent.
                    >
                    > So it works but only saves one address for each parent, that is the
                    > last one in the XML file. I have been able to get it to do one to
                    > many but with only one parent table, not two.
                    >
                    > I feel that I am really pushing your patience but I have a large
                    > project that I am using your program for and need to work this stuff
                    > out. The real project has around 14 tables and is reasonably complex
                    > so it is talking me a bit to get my head around it all. I am very
                    > grateful for all the help you have given me.
                  • Ronald Bourret
                    I am not aware of any such tool. If you find one, please let us know :) And if you re interested in writing one, XML-DBMS s classes were designed with such a
                    Message 9 of 17 , Aug 14, 2005
                    • 0 Attachment
                      I am not aware of any such tool. If you find one, please let us know :)
                      And if you're interested in writing one, XML-DBMS's classes were
                      designed with such a tool in mind.

                      -- Ron

                      Hiral Shah wrote:

                      > Hi everyone,
                      > I am a student working towards my masters project in
                      > XML. i was wondering if there was any open source tool
                      > with features like stylus studio's mapping facility in
                      > the market.
                    • Jabir Ahmed
                      Hello I would be interested in writing such a tool regards Jabir Ahmed ... From: Ronald Bourret To: xml-dbms@yahoogroups.com Sent: Monday, August 15, 2005
                      Message 10 of 17 , Aug 15, 2005
                      • 0 Attachment
                        Hello
                        I would be interested in writing such a tool

                        regards

                        Jabir Ahmed
                        ----- Original Message -----
                        From: Ronald Bourret
                        To: xml-dbms@yahoogroups.com
                        Sent: Monday, August 15, 2005 12:09 PM
                        Subject: Re: [xml-dbms] open source mapping tool


                        I am not aware of any such tool. If you find one, please let us know :)
                        And if you're interested in writing one, XML-DBMS's classes were
                        designed with such a tool in mind.

                        -- Ron

                        Hiral Shah wrote:

                        > Hi everyone,
                        > I am a student working towards my masters project in
                        > XML. i was wondering if there was any open source tool
                        > with features like stylus studio's mapping facility in
                        > the market.



                        To post a message, send it to: xml-dbms@yahoogroups.com
                        To unsubscribe, send a blank message to: xml-dbms-unsubscribe@yahoogroups.com



                        ------------------------------------------------------------------------------
                        YAHOO! GROUPS LINKS

                        a.. Visit your group "xml-dbms" on the web.

                        b.. To unsubscribe from this group, send an email to:
                        xml-dbms-unsubscribe@yahoogroups.com

                        c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


                        ------------------------------------------------------------------------------



                        [Non-text portions of this message have been removed]
                      • sctt_bainbridge
                        Again thanks for your help. I will try this and let you know how I go. Cheers, Scott.
                        Message 11 of 17 , Aug 15, 2005
                        • 0 Attachment
                          Again thanks for your help. I will try this and let you know how I go.


                          Cheers,



                          Scott.


                          --- In xml-dbms@yahoogroups.com, Ronald Bourret <rpbourret@r...> wrote:
                          > Hello,
                          >
                        • sctt_bainbridge
                          Hi, this is what I am using now: ... 12 Scott
                          Message 12 of 17 , Aug 15, 2005
                          • 0 Attachment
                            Hi,

                            this is what I am using now:

                            XML File:
                            ---------------
                            <?xml version="1.0" encoding="ISO-8859-1" ?>
                            <contact>
                            <contactId>12</contactId>
                            <firstName>Scott</firstName>
                            <lastName>Bainbridge</lastName>
                            <addresses>
                            <address>
                            <addressId>1</addressId>
                            <street>123 My Street</street>
                            <city>My Town</city>
                            </address>
                            <address>
                            <addressId>2</addressId>
                            <street>456 Another Street</street>
                            <city>My Town</city>
                            </address>
                            </addresses>
                            <workDetails>
                            <contactId>12</contactId>
                            <companyName>This Company</companyName>
                            <companyType>Local</companyType>
                            <incorporated>N</incorporated>
                            <addresses>
                            <address>
                            <addressId>4</addressId>
                            <street>123 My Street</street>
                            <city>My Town</city>
                            </address>
                            <address>
                            <addressId>5</addressId>
                            <street>456 Another Street</street>
                            <city>My Town</city>
                            </address>
                            </addresses>
                            </workDetails>
                            </contact>

                            ---------------

                            Map File
                            ---------------
                            <?xml version='1.0' ?>

                            <!DOCTYPE XMLToDBMS SYSTEM "xmldbms2.dtd" >

                            <XMLToDBMS Version="2.0"
                            xmlns="http://www.xmlmiddleware.org/xmldbms/v2">
                            <Options>
                            <SimpleDateFormat Pattern="yyyy-MM-dd"
                            DefaultForTypes="DATE" />
                            </Options>
                            <Databases>
                            <Database>
                            <Catalog>
                            <Schema>
                            <Table Name="XFORM_CONTACT_WORK_DETAILS">
                            <Column Name="CONTACT_ID" DataType="INTEGER"
                            Nullable="No"/>
                            <Column Name="COMPANY_NAME"
                            DataType="VARCHAR" Length="150" Nullable="Yes"/>
                            <Column Name="COMPANY_TYPE"
                            DataType="VARCHAR" Length="50" Nullable="Yes"/>
                            <Column Name="INCORPORATED"
                            DataType="VARCHAR" Length="1" Nullable="Yes"/>
                            <PrimaryKey Name="PK_CONTACT_WORK">
                            <UseColumn Name="CONTACT_ID"/>
                            </PrimaryKey>
                            </Table>
                            <Table Name="XFORM_CONTACT">
                            <Column Name="CONTACT_ID" DataType="INTEGER"
                            Nullable="No"/>
                            <Column Name="FIRST_NAME" DataType="VARCHAR"
                            Length="250" Nullable="Yes"/>
                            <Column Name="LAST_NAME" DataType="VARCHAR"
                            Length="250" Nullable="Yes"/>
                            <PrimaryKey Name="PK_CONTACT">
                            <UseColumn Name="CONTACT_ID"/>
                            </PrimaryKey>
                            <ForeignKey Name="FK_CONTACT_WORK">
                            <UseTable
                            Name="XFORM_CONTACT_WORK_DETAILS" />
                            <UseUniqueKey Name="PK_CONTACT_WORK" />
                            <UseColumn Name="CONTACT_ID"/>
                            </ForeignKey>
                            </Table>
                            <Table Name="XFORM_CONTACT_ADDRESS">
                            <Column Name="ADDRESS_ID" DataType="INTEGER"
                            Nullable="No"/>
                            <Column Name="STREET" DataType="VARCHAR"
                            Length="50" Nullable="Yes"/>
                            <Column Name="CITY" DataType="VARCHAR"
                            Length="50" Nullable="Yes"/>
                            <PrimaryKey Name="PK_CONTACT_ADDRESS">
                            <UseColumn Name="ADDRESS_ID"/>
                            </PrimaryKey>
                            </Table>
                            <Table Name="XFORM_CONTACT_LINK_ADDRESS">
                            <Column Name="CONTACT_ID" DataType="INTEGER"
                            Nullable="No"/>
                            <Column Name="ADDRESS_ID" DataType="INTEGER"
                            Nullable="No"/>
                            <PrimaryKey Name="PK_CONTACT_LINK_ADDRESS">
                            <UseColumn Name="CONTACT_ID" />
                            <UseColumn Name="ADDRESS_ID" />
                            </PrimaryKey>
                            <ForeignKey Name="FK_CONTACT_ID">
                            <UseTable Name="XFORM_CONTACT" />
                            <UseUniqueKey Name="PK_CONTACT"/>
                            <UseColumn Name="CONTACT_ID" />
                            </ForeignKey>
                            <ForeignKey Name="FK_CONTACT_WORK_ID">
                            <UseTable
                            Name="XFORM_CONTACT_WORK_DETAILS" />
                            <UseUniqueKey Name="PK_CONTACT_WORK" />
                            <UseColumn Name="CONTACT_ID"/>
                            </ForeignKey>
                            <ForeignKey Name="FK_ADDRESS_ID">
                            <UseTable Name="XFORM_CONTACT_ADDRESS" />
                            <UseUniqueKey Name="PK_CONTACT_ADDRESS" />
                            <UseColumn Name="ADDRESS_ID"/>
                            </ForeignKey>
                            </Table>
                            </Schema>
                            </Catalog>
                            </Database>
                            </Databases>
                            <Maps>
                            <ClassMap>
                            <ElementType Name="workDetails"/>
                            <ToClassTable Name="XFORM_CONTACT_WORK_DETAILS"/>
                            <PropertyMap>
                            <ElementType Name="contactId"/>
                            <ToColumn Name="CONTACT_ID"/>
                            </PropertyMap>
                            <PropertyMap>
                            <ElementType Name="companyName"/>
                            <ToColumn Name="COMPANY_NAME"/>
                            </PropertyMap>
                            <PropertyMap>
                            <ElementType Name="companyType"/>
                            <ToColumn Name="COMPANY_TYPE"/>
                            </PropertyMap>
                            <PropertyMap>
                            <ElementType Name="incorporated"/>
                            <ToColumn Name="INCORPORATED"/>
                            </PropertyMap>
                            <RelatedClass KeyInParentTable="Unique">
                            <ElementType Name="addresses"/>
                            <UseUniqueKey Name="PK_CONTACT_WORK"/>
                            <UseForeignKey Name="FK_CONTACT_WORK_ID"/>
                            </RelatedClass>
                            </ClassMap>
                            <ClassMap>
                            <ElementType Name="contact"/>
                            <ToClassTable Name="XFORM_CONTACT"/>
                            <PropertyMap>
                            <ElementType Name="contactId"/>
                            <ToColumn Name="CONTACT_ID"/>
                            </PropertyMap>
                            <PropertyMap>
                            <ElementType Name="firstName"/>
                            <ToColumn Name="FIRST_NAME"/>
                            </PropertyMap>
                            <PropertyMap>
                            <ElementType Name="lastName"/>
                            <ToColumn Name="LAST_NAME"/>
                            </PropertyMap>
                            <RelatedClass KeyInParentTable="Foreign">
                            <ElementType Name="workDetails"/>
                            <UseUniqueKey Name="PK_CONTACT_WORK"/>
                            <UseForeignKey Name="FK_CONTACT_WORK"/>
                            </RelatedClass>
                            <RelatedClass KeyInParentTable="Unique">
                            <ElementType Name="addresses"/>
                            <UseUniqueKey Name="PK_CONTACT"/>
                            <UseForeignKey Name="FK_CONTACT_ID"/>
                            </RelatedClass>
                            </ClassMap>
                            <ClassMap>
                            <ElementType Name="addresses"/>
                            <ToClassTable Name="XFORM_CONTACT_LINK_ADDRESS"/>
                            <RelatedClass KeyInParentTable="Foreign">
                            <ElementType Name="address"/>
                            <UseUniqueKey Name="PK_CONTACT_ADDRESS"/>
                            <UseForeignKey Name="FK_ADDRESS_ID"/>
                            </RelatedClass>
                            </ClassMap>
                            <ClassMap>
                            <ElementType Name="address"/>
                            <ToClassTable Name="XFORM_CONTACT_ADDRESS"/>
                            <PropertyMap>
                            <ElementType Name="addressId"/>
                            <ToColumn Name="ADDRESS_ID"/>
                            </PropertyMap>
                            <PropertyMap>
                            <ElementType Name="street"/>
                            <ToColumn Name="STREET"/>
                            </PropertyMap>
                            <PropertyMap>
                            <ElementType Name="city"/>
                            <ToColumn Name="CITY"/>
                            </PropertyMap>
                            </ClassMap>
                            </Maps>
                            </XMLToDBMS>

                            ---------------

                            Database tables:
                            ---------------
                            create table xform_contact (
                            contact_id integer not null,
                            first_name varchar(250),
                            last_name varchar(250),
                            CONSTRAINT pk_contact PRIMARY KEY (contact_id),
                            CONSTRAINT fk_contact_work FOREIGN KEY (contact_id)
                            REFERENCES xform_contact_work_details(contact_id));


                            drop table xform_contact_work_details;

                            commit;

                            create table xform_contact_work_details (
                            contact_id integer not null,
                            company_name varchar(150),
                            company_type varchar(50),
                            incorporated varchar(1),
                            CONSTRAINT pk_contact_work PRIMARY KEY (contact_id));

                            commit;


                            drop table xform_contact_address;

                            commit;

                            create table xform_contact_address (
                            address_id integer not null,
                            street varchar(250),
                            city varchar(100),
                            CONSTRAINT pk_contact_address PRIMARY KEY (address_id));

                            commit;


                            create table xform_contact_link_address (
                            contact_id integer not null,
                            address_id integer not null,
                            CONSTRAINT pk_contact_link_address PRIMARY KEY
                            (contact_id,address_id),
                            CONSTRAINT fk_contact_id FOREIGN KEY (contact_id)
                            REFERENCES xform_contact(contact_id),
                            CONSTRAINT fk_contact_work_id FOREIGN KEY (contact_id)
                            REFERENCES xform_contact_work_details(contact_id),
                            CONSTRAINT fk_address_id FOREIGN KEY (address_id)
                            REFERENCES xform_contact_address(address_id));


                            ---------------

                            This gives me the error:

                            java.sql.SQLException: ORA-02291: integrity constraint
                            (DATACENTRE.FK_CONTACT_ID) violated - parent key not found

                            I am in theprocess of trying to debug this but thought you might be
                            able to spot something wrong easily, no probs if not I will work on
                            this some more tonight.


                            Cheers,



                            Scott.
                          • sctt_bainbridge
                            Hi, Just had a thought - do I need two linking tables, one for the contact to address link and the other for the work details to address link?? I was trying to
                            Message 13 of 17 , Aug 16, 2005
                            • 0 Attachment
                              Hi,

                              Just had a thought - do I need two linking tables, one for the
                              contact to address link and the other for the work details to
                              address link?? I was trying to use the one table for both links,
                              that is having three foreign keys, one pointing to the contacts, one
                              to the work details and the third to the addresses table.

                              Also I have set the type of key to Unique for the contact to address
                              link table and foreign from the address link table to the addresses
                              table in the map file. Is this correct??

                              Thanks,



                              Scott.

                              --- In xml-dbms@yahoogroups.com, "sctt_bainbridge"
                              <s.bainbridge@a...> wrote:
                              > Hi,
                              >
                              > this is what I am using now:
                              >
                              > XML File:
                              > ---------------
                              > <?xml version="1.0" encoding="ISO-8859-1" ?>
                              > <contact>
                              > <contactId>12</contactId>
                              > <firstName>Scott</firstName>
                              > <lastName>Bainbridge</lastName>
                              > <addresses>
                              > <address>
                              > <addressId>1</addressId>
                              > <street>123 My Street</street>
                              > <city>My Town</city>
                              > </address>
                              > <address>
                              > <addressId>2</addressId>
                              > <street>456 Another Street</street>
                              > <city>My Town</city>
                              > </address>
                              > </addresses>
                              > <workDetails>
                              > <contactId>12</contactId>
                              > <companyName>This Company</companyName>
                              > <companyType>Local</companyType>
                              > <incorporated>N</incorporated>
                              > <addresses>
                              > <address>
                              > <addressId>4</addressId>
                              > <street>123 My Street</street>
                              > <city>My Town</city>
                              > </address>
                              > <address>
                              > <addressId>5</addressId>
                              > <street>456 Another Street</street>
                              > <city>My Town</city>
                              > </address>
                              > </addresses>
                              > </workDetails>
                              > </contact>
                              >
                              > ---------------
                              >
                              > Map File
                              > ---------------
                              > <?xml version='1.0' ?>
                              >
                              > <!DOCTYPE XMLToDBMS SYSTEM "xmldbms2.dtd" >
                              >
                              > <XMLToDBMS Version="2.0"
                              > xmlns="http://www.xmlmiddleware.org/xmldbms/v2">
                              > <Options>
                              > <SimpleDateFormat Pattern="yyyy-MM-dd"
                              > DefaultForTypes="DATE" />
                              > </Options>
                              > <Databases>
                              > <Database>
                              > <Catalog>
                              > <Schema>
                              > <Table Name="XFORM_CONTACT_WORK_DETAILS">
                              > <Column Name="CONTACT_ID"
                              DataType="INTEGER"
                              > Nullable="No"/>
                              > <Column Name="COMPANY_NAME"
                              > DataType="VARCHAR" Length="150" Nullable="Yes"/>
                              > <Column Name="COMPANY_TYPE"
                              > DataType="VARCHAR" Length="50" Nullable="Yes"/>
                              > <Column Name="INCORPORATED"
                              > DataType="VARCHAR" Length="1" Nullable="Yes"/>
                              > <PrimaryKey Name="PK_CONTACT_WORK">
                              > <UseColumn Name="CONTACT_ID"/>
                              > </PrimaryKey>
                              > </Table>
                              > <Table Name="XFORM_CONTACT">
                              > <Column Name="CONTACT_ID"
                              DataType="INTEGER"
                              > Nullable="No"/>
                              > <Column Name="FIRST_NAME"
                              DataType="VARCHAR"
                              > Length="250" Nullable="Yes"/>
                              > <Column Name="LAST_NAME"
                              DataType="VARCHAR"
                              > Length="250" Nullable="Yes"/>
                              > <PrimaryKey Name="PK_CONTACT">
                              > <UseColumn Name="CONTACT_ID"/>
                              > </PrimaryKey>
                              > <ForeignKey Name="FK_CONTACT_WORK">
                              > <UseTable
                              > Name="XFORM_CONTACT_WORK_DETAILS" />
                              > <UseUniqueKey Name="PK_CONTACT_WORK" />
                              > <UseColumn Name="CONTACT_ID"/>
                              > </ForeignKey>
                              > </Table>
                              > <Table Name="XFORM_CONTACT_ADDRESS">
                              > <Column Name="ADDRESS_ID"
                              DataType="INTEGER"
                              > Nullable="No"/>
                              > <Column Name="STREET" DataType="VARCHAR"
                              > Length="50" Nullable="Yes"/>
                              > <Column Name="CITY" DataType="VARCHAR"
                              > Length="50" Nullable="Yes"/>
                              > <PrimaryKey Name="PK_CONTACT_ADDRESS">
                              > <UseColumn Name="ADDRESS_ID"/>
                              > </PrimaryKey>
                              > </Table>
                              > <Table Name="XFORM_CONTACT_LINK_ADDRESS">
                              > <Column Name="CONTACT_ID"
                              DataType="INTEGER"
                              > Nullable="No"/>
                              > <Column Name="ADDRESS_ID"
                              DataType="INTEGER"
                              > Nullable="No"/>
                              > <PrimaryKey Name="PK_CONTACT_LINK_ADDRESS">
                              > <UseColumn Name="CONTACT_ID" />
                              > <UseColumn Name="ADDRESS_ID" />
                              > </PrimaryKey>
                              > <ForeignKey Name="FK_CONTACT_ID">
                              > <UseTable Name="XFORM_CONTACT" />
                              > <UseUniqueKey Name="PK_CONTACT"/>
                              > <UseColumn Name="CONTACT_ID" />
                              > </ForeignKey>
                              > <ForeignKey Name="FK_CONTACT_WORK_ID">
                              > <UseTable
                              > Name="XFORM_CONTACT_WORK_DETAILS" />
                              > <UseUniqueKey Name="PK_CONTACT_WORK" />
                              > <UseColumn Name="CONTACT_ID"/>
                              > </ForeignKey>
                              > <ForeignKey Name="FK_ADDRESS_ID">
                              > <UseTable
                              Name="XFORM_CONTACT_ADDRESS" />
                              > <UseUniqueKey
                              Name="PK_CONTACT_ADDRESS" />
                              > <UseColumn Name="ADDRESS_ID"/>
                              > </ForeignKey>
                              > </Table>
                              > </Schema>
                              > </Catalog>
                              > </Database>
                              > </Databases>
                              > <Maps>
                              > <ClassMap>
                              > <ElementType Name="workDetails"/>
                              > <ToClassTable Name="XFORM_CONTACT_WORK_DETAILS"/>
                              > <PropertyMap>
                              > <ElementType Name="contactId"/>
                              > <ToColumn Name="CONTACT_ID"/>
                              > </PropertyMap>
                              > <PropertyMap>
                              > <ElementType Name="companyName"/>
                              > <ToColumn Name="COMPANY_NAME"/>
                              > </PropertyMap>
                              > <PropertyMap>
                              > <ElementType Name="companyType"/>
                              > <ToColumn Name="COMPANY_TYPE"/>
                              > </PropertyMap>
                              > <PropertyMap>
                              > <ElementType Name="incorporated"/>
                              > <ToColumn Name="INCORPORATED"/>
                              > </PropertyMap>
                              > <RelatedClass KeyInParentTable="Unique">
                              > <ElementType Name="addresses"/>
                              > <UseUniqueKey Name="PK_CONTACT_WORK"/>
                              > <UseForeignKey Name="FK_CONTACT_WORK_ID"/>
                              > </RelatedClass>
                              > </ClassMap>
                              > <ClassMap>
                              > <ElementType Name="contact"/>
                              > <ToClassTable Name="XFORM_CONTACT"/>
                              > <PropertyMap>
                              > <ElementType Name="contactId"/>
                              > <ToColumn Name="CONTACT_ID"/>
                              > </PropertyMap>
                              > <PropertyMap>
                              > <ElementType Name="firstName"/>
                              > <ToColumn Name="FIRST_NAME"/>
                              > </PropertyMap>
                              > <PropertyMap>
                              > <ElementType Name="lastName"/>
                              > <ToColumn Name="LAST_NAME"/>
                              > </PropertyMap>
                              > <RelatedClass KeyInParentTable="Foreign">
                              > <ElementType Name="workDetails"/>
                              > <UseUniqueKey Name="PK_CONTACT_WORK"/>
                              > <UseForeignKey Name="FK_CONTACT_WORK"/>
                              > </RelatedClass>
                              > <RelatedClass KeyInParentTable="Unique">
                              > <ElementType Name="addresses"/>
                              > <UseUniqueKey Name="PK_CONTACT"/>
                              > <UseForeignKey Name="FK_CONTACT_ID"/>
                              > </RelatedClass>
                              > </ClassMap>
                              > <ClassMap>
                              > <ElementType Name="addresses"/>
                              > <ToClassTable Name="XFORM_CONTACT_LINK_ADDRESS"/>
                              > <RelatedClass KeyInParentTable="Foreign">
                              > <ElementType Name="address"/>
                              > <UseUniqueKey Name="PK_CONTACT_ADDRESS"/>
                              > <UseForeignKey Name="FK_ADDRESS_ID"/>
                              > </RelatedClass>
                              > </ClassMap>
                              > <ClassMap>
                              > <ElementType Name="address"/>
                              > <ToClassTable Name="XFORM_CONTACT_ADDRESS"/>
                              > <PropertyMap>
                              > <ElementType Name="addressId"/>
                              > <ToColumn Name="ADDRESS_ID"/>
                              > </PropertyMap>
                              > <PropertyMap>
                              > <ElementType Name="street"/>
                              > <ToColumn Name="STREET"/>
                              > </PropertyMap>
                              > <PropertyMap>
                              > <ElementType Name="city"/>
                              > <ToColumn Name="CITY"/>
                              > </PropertyMap>
                              > </ClassMap>
                              > </Maps>
                              > </XMLToDBMS>
                              >
                              > ---------------
                              >
                              > Database tables:
                              > ---------------
                              > create table xform_contact (
                              > contact_id integer not null,
                              > first_name varchar(250),
                              > last_name varchar(250),
                              > CONSTRAINT pk_contact PRIMARY KEY (contact_id),
                              > CONSTRAINT fk_contact_work FOREIGN KEY (contact_id)
                              > REFERENCES xform_contact_work_details(contact_id));
                              >
                              >
                              > drop table xform_contact_work_details;
                              >
                              > commit;
                              >
                              > create table xform_contact_work_details (
                              > contact_id integer not null,
                              > company_name varchar(150),
                              > company_type varchar(50),
                              > incorporated varchar(1),
                              > CONSTRAINT pk_contact_work PRIMARY KEY (contact_id));
                              >
                              > commit;
                              >
                              >
                              > drop table xform_contact_address;
                              >
                              > commit;
                              >
                              > create table xform_contact_address (
                              > address_id integer not null,
                              > street varchar(250),
                              > city varchar(100),
                              > CONSTRAINT pk_contact_address PRIMARY KEY (address_id));
                              >
                              > commit;
                              >
                              >
                              > create table xform_contact_link_address (
                              > contact_id integer not null,
                              > address_id integer not null,
                              > CONSTRAINT pk_contact_link_address PRIMARY KEY
                              > (contact_id,address_id),
                              > CONSTRAINT fk_contact_id FOREIGN KEY (contact_id)
                              > REFERENCES xform_contact(contact_id),
                              > CONSTRAINT fk_contact_work_id FOREIGN KEY (contact_id)
                              > REFERENCES xform_contact_work_details(contact_id),
                              > CONSTRAINT fk_address_id FOREIGN KEY (address_id)
                              > REFERENCES xform_contact_address(address_id));
                              >
                              >
                              > ---------------
                              >
                              > This gives me the error:
                              >
                              > java.sql.SQLException: ORA-02291: integrity constraint
                              > (DATACENTRE.FK_CONTACT_ID) violated - parent key not found
                              >
                              > I am in theprocess of trying to debug this but thought you might
                              be
                              > able to spot something wrong easily, no probs if not I will work
                              on
                              > this some more tonight.
                              >
                              >
                              > Cheers,
                              >
                              >
                              >
                              > Scott.
                            • sctt_bainbridge
                              I think the problem is that as I am using a one to one relationship between the contacts and work details elements they are both putting the same contact_id in
                              Message 14 of 17 , Aug 16, 2005
                              • 0 Attachment
                                I think the problem is that as I am using a one to one relationship
                                between the contacts and work details elements they are both putting
                                the same contact_id in the link table and so it can't match
                                addresses to parents.

                                If I use a second linking column to link it to contact_id and, say,
                                parent_id then it should know which parent each address belongs to.

                                At the moment I have the following:

                                If the contact_id is 15 and the addresses for the contact element
                                are address_id 2 and 3 then the link table will store:
                                15,2
                                15,3

                                in the contact_id and address_id fields, when the work details
                                elements stores its addresses it also has a contact_id of 15 but
                                migth store addresses 5 and 6 and so the link table would have:

                                15,5
                                15,6

                                Which means that from the link table you can't tell what parent each
                                one belongs to, that is what addresses belong to the contact
                                elements and what ones belong to the work details element.

                                If I add a parent_id field then that should resolve the problem, so
                                then we would have:

                                Contact address as Contact_id = 15, parent_id = 1, addresses 2 and 3
                                as:

                                15,1,2
                                15,1,3

                                For the work details this might be contact_id = 15, parent_id = 2,
                                address_id = 5 and 6 so:

                                15,2,5
                                15,2,6

                                which should be Ok, this would presumably also deal with where the
                                same address is used by both elements, e.g.:

                                15,1,4
                                15,2,4

                                If address #4 was in both the contact and work details elements.

                                Am I on the right track here??? It seems that the link table solves
                                teh many to many but as I have it not the mutiple elements inteh one
                                table issue.

                                Again any thoughts would help.


                                SCott.


                                --- In xml-dbms@yahoogroups.com, "sctt_bainbridge"
                                <s.bainbridge@a...> wrote:
                                > Hi,
                                >
                                > Just had a thought - do I need two linking tables, one for the
                                > contact to address link and the other for the work details to
                                > address link?? I was trying to use the one table for both links,
                                > that is having three foreign keys, one pointing to the contacts,
                                one
                                > to the work details and the third to the addresses table.
                                >
                                > Also I have set the type of key to Unique for the contact to
                                address
                                > link table and foreign from the address link table to the
                                addresses
                                > table in the map file. Is this correct??
                                >
                                > Thanks,
                                >
                                >
                                >
                                > Scott.
                                >
                                > --- In xml-dbms@yahoogroups.com, "sctt_bainbridge"
                                > <s.bainbridge@a...> wrote:
                                > > Hi,
                                > >
                                > > this is what I am using now:
                                > >
                                > > XML File:
                                > > ---------------
                                > > <?xml version="1.0" encoding="ISO-8859-1" ?>
                                > > <contact>
                                > > <contactId>12</contactId>
                                > > <firstName>Scott</firstName>
                                > > <lastName>Bainbridge</lastName>
                                > > <addresses>
                                > > <address>
                                > > <addressId>1</addressId>
                                > > <street>123 My Street</street>
                                > > <city>My Town</city>
                                > > </address>
                                > > <address>
                                > > <addressId>2</addressId>
                                > > <street>456 Another Street</street>
                                > > <city>My Town</city>
                                > > </address>
                                > > </addresses>
                                > > <workDetails>
                                > > <contactId>12</contactId>
                                > > <companyName>This Company</companyName>
                                > > <companyType>Local</companyType>
                                > > <incorporated>N</incorporated>
                                > > <addresses>
                                > > <address>
                                > > <addressId>4</addressId>
                                > > <street>123 My Street</street>
                                > > <city>My Town</city>
                                > > </address>
                                > > <address>
                                > > <addressId>5</addressId>
                                > > <street>456 Another Street</street>
                                > > <city>My Town</city>
                                > > </address>
                                > > </addresses>
                                > > </workDetails>
                                > > </contact>
                                > >
                                > > ---------------
                                > >
                                > > Map File
                                > > ---------------
                                > > <?xml version='1.0' ?>
                                > >
                                > > <!DOCTYPE XMLToDBMS SYSTEM "xmldbms2.dtd" >
                                > >
                                > > <XMLToDBMS Version="2.0"
                                > > xmlns="http://www.xmlmiddleware.org/xmldbms/v2">
                                > > <Options>
                                > > <SimpleDateFormat Pattern="yyyy-MM-dd"
                                > > DefaultForTypes="DATE" />
                                > > </Options>
                                > > <Databases>
                                > > <Database>
                                > > <Catalog>
                                > > <Schema>
                                > > <Table Name="XFORM_CONTACT_WORK_DETAILS">
                                > > <Column Name="CONTACT_ID"
                                > DataType="INTEGER"
                                > > Nullable="No"/>
                                > > <Column Name="COMPANY_NAME"
                                > > DataType="VARCHAR" Length="150" Nullable="Yes"/>
                                > > <Column Name="COMPANY_TYPE"
                                > > DataType="VARCHAR" Length="50" Nullable="Yes"/>
                                > > <Column Name="INCORPORATED"
                                > > DataType="VARCHAR" Length="1" Nullable="Yes"/>
                                > > <PrimaryKey Name="PK_CONTACT_WORK">
                                > > <UseColumn Name="CONTACT_ID"/>
                                > > </PrimaryKey>
                                > > </Table>
                                > > <Table Name="XFORM_CONTACT">
                                > > <Column Name="CONTACT_ID"
                                > DataType="INTEGER"
                                > > Nullable="No"/>
                                > > <Column Name="FIRST_NAME"
                                > DataType="VARCHAR"
                                > > Length="250" Nullable="Yes"/>
                                > > <Column Name="LAST_NAME"
                                > DataType="VARCHAR"
                                > > Length="250" Nullable="Yes"/>
                                > > <PrimaryKey Name="PK_CONTACT">
                                > > <UseColumn Name="CONTACT_ID"/>
                                > > </PrimaryKey>
                                > > <ForeignKey Name="FK_CONTACT_WORK">
                                > > <UseTable
                                > > Name="XFORM_CONTACT_WORK_DETAILS" />
                                > > <UseUniqueKey
                                Name="PK_CONTACT_WORK" />
                                > > <UseColumn Name="CONTACT_ID"/>
                                > > </ForeignKey>
                                > > </Table>
                                > > <Table Name="XFORM_CONTACT_ADDRESS">
                                > > <Column Name="ADDRESS_ID"
                                > DataType="INTEGER"
                                > > Nullable="No"/>
                                > > <Column Name="STREET" DataType="VARCHAR"
                                > > Length="50" Nullable="Yes"/>
                                > > <Column Name="CITY" DataType="VARCHAR"
                                > > Length="50" Nullable="Yes"/>
                                > > <PrimaryKey Name="PK_CONTACT_ADDRESS">
                                > > <UseColumn Name="ADDRESS_ID"/>
                                > > </PrimaryKey>
                                > > </Table>
                                > > <Table Name="XFORM_CONTACT_LINK_ADDRESS">
                                > > <Column Name="CONTACT_ID"
                                > DataType="INTEGER"
                                > > Nullable="No"/>
                                > > <Column Name="ADDRESS_ID"
                                > DataType="INTEGER"
                                > > Nullable="No"/>
                                > > <PrimaryKey
                                Name="PK_CONTACT_LINK_ADDRESS">
                                > > <UseColumn Name="CONTACT_ID" />
                                > > <UseColumn Name="ADDRESS_ID" />
                                > > </PrimaryKey>
                                > > <ForeignKey Name="FK_CONTACT_ID">
                                > > <UseTable Name="XFORM_CONTACT" />
                                > > <UseUniqueKey Name="PK_CONTACT"/>
                                > > <UseColumn Name="CONTACT_ID" />
                                > > </ForeignKey>
                                > > <ForeignKey Name="FK_CONTACT_WORK_ID">
                                > > <UseTable
                                > > Name="XFORM_CONTACT_WORK_DETAILS" />
                                > > <UseUniqueKey
                                Name="PK_CONTACT_WORK" />
                                > > <UseColumn Name="CONTACT_ID"/>
                                > > </ForeignKey>
                                > > <ForeignKey Name="FK_ADDRESS_ID">
                                > > <UseTable
                                > Name="XFORM_CONTACT_ADDRESS" />
                                > > <UseUniqueKey
                                > Name="PK_CONTACT_ADDRESS" />
                                > > <UseColumn Name="ADDRESS_ID"/>
                                > > </ForeignKey>
                                > > </Table>
                                > > </Schema>
                                > > </Catalog>
                                > > </Database>
                                > > </Databases>
                                > > <Maps>
                                > > <ClassMap>
                                > > <ElementType Name="workDetails"/>
                                > > <ToClassTable Name="XFORM_CONTACT_WORK_DETAILS"/>
                                > > <PropertyMap>
                                > > <ElementType Name="contactId"/>
                                > > <ToColumn Name="CONTACT_ID"/>
                                > > </PropertyMap>
                                > > <PropertyMap>
                                > > <ElementType Name="companyName"/>
                                > > <ToColumn Name="COMPANY_NAME"/>
                                > > </PropertyMap>
                                > > <PropertyMap>
                                > > <ElementType Name="companyType"/>
                                > > <ToColumn Name="COMPANY_TYPE"/>
                                > > </PropertyMap>
                                > > <PropertyMap>
                                > > <ElementType Name="incorporated"/>
                                > > <ToColumn Name="INCORPORATED"/>
                                > > </PropertyMap>
                                > > <RelatedClass KeyInParentTable="Unique">
                                > > <ElementType Name="addresses"/>
                                > > <UseUniqueKey Name="PK_CONTACT_WORK"/>
                                > > <UseForeignKey Name="FK_CONTACT_WORK_ID"/>
                                > > </RelatedClass>
                                > > </ClassMap>
                                > > <ClassMap>
                                > > <ElementType Name="contact"/>
                                > > <ToClassTable Name="XFORM_CONTACT"/>
                                > > <PropertyMap>
                                > > <ElementType Name="contactId"/>
                                > > <ToColumn Name="CONTACT_ID"/>
                                > > </PropertyMap>
                                > > <PropertyMap>
                                > > <ElementType Name="firstName"/>
                                > > <ToColumn Name="FIRST_NAME"/>
                                > > </PropertyMap>
                                > > <PropertyMap>
                                > > <ElementType Name="lastName"/>
                                > > <ToColumn Name="LAST_NAME"/>
                                > > </PropertyMap>
                                > > <RelatedClass KeyInParentTable="Foreign">
                                > > <ElementType Name="workDetails"/>
                                > > <UseUniqueKey Name="PK_CONTACT_WORK"/>
                                > > <UseForeignKey Name="FK_CONTACT_WORK"/>
                                > > </RelatedClass>
                                > > <RelatedClass KeyInParentTable="Unique">
                                > > <ElementType Name="addresses"/>
                                > > <UseUniqueKey Name="PK_CONTACT"/>
                                > > <UseForeignKey Name="FK_CONTACT_ID"/>
                                > > </RelatedClass>
                                > > </ClassMap>
                                > > <ClassMap>
                                > > <ElementType Name="addresses"/>
                                > > <ToClassTable Name="XFORM_CONTACT_LINK_ADDRESS"/>
                                > > <RelatedClass KeyInParentTable="Foreign">
                                > > <ElementType Name="address"/>
                                > > <UseUniqueKey Name="PK_CONTACT_ADDRESS"/>
                                > > <UseForeignKey Name="FK_ADDRESS_ID"/>
                                > > </RelatedClass>
                                > > </ClassMap>
                                > > <ClassMap>
                                > > <ElementType Name="address"/>
                                > > <ToClassTable Name="XFORM_CONTACT_ADDRESS"/>
                                > > <PropertyMap>
                                > > <ElementType Name="addressId"/>
                                > > <ToColumn Name="ADDRESS_ID"/>
                                > > </PropertyMap>
                                > > <PropertyMap>
                                > > <ElementType Name="street"/>
                                > > <ToColumn Name="STREET"/>
                                > > </PropertyMap>
                                > > <PropertyMap>
                                > > <ElementType Name="city"/>
                                > > <ToColumn Name="CITY"/>
                                > > </PropertyMap>
                                > > </ClassMap>
                                > > </Maps>
                                > > </XMLToDBMS>
                                > >
                                > > ---------------
                                > >
                                > > Database tables:
                                > > ---------------
                                > > create table xform_contact (
                                > > contact_id integer not null,
                                > > first_name varchar(250),
                                > > last_name varchar(250),
                                > > CONSTRAINT pk_contact PRIMARY KEY (contact_id),
                                > > CONSTRAINT fk_contact_work FOREIGN KEY (contact_id)
                                > > REFERENCES xform_contact_work_details(contact_id));
                                > >
                                > >
                                > > drop table xform_contact_work_details;
                                > >
                                > > commit;
                                > >
                                > > create table xform_contact_work_details (
                                > > contact_id integer not null,
                                > > company_name varchar(150),
                                > > company_type varchar(50),
                                > > incorporated varchar(1),
                                > > CONSTRAINT pk_contact_work PRIMARY KEY (contact_id));
                                > >
                                > > commit;
                                > >
                                > >
                                > > drop table xform_contact_address;
                                > >
                                > > commit;
                                > >
                                > > create table xform_contact_address (
                                > > address_id integer not null,
                                > > street varchar(250),
                                > > city varchar(100),
                                > > CONSTRAINT pk_contact_address PRIMARY KEY (address_id));
                                > >
                                > > commit;
                                > >
                                > >
                                > > create table xform_contact_link_address (
                                > > contact_id integer not null,
                                > > address_id integer not null,
                                > > CONSTRAINT pk_contact_link_address PRIMARY KEY
                                > > (contact_id,address_id),
                                > > CONSTRAINT fk_contact_id FOREIGN KEY (contact_id)
                                > > REFERENCES xform_contact(contact_id),
                                > > CONSTRAINT fk_contact_work_id FOREIGN KEY (contact_id)
                                > > REFERENCES xform_contact_work_details(contact_id),
                                > > CONSTRAINT fk_address_id FOREIGN KEY (address_id)
                                > > REFERENCES xform_contact_address(address_id));
                                > >
                                > >
                                > > ---------------
                                > >
                                > > This gives me the error:
                                > >
                                > > java.sql.SQLException: ORA-02291: integrity constraint
                                > > (DATACENTRE.FK_CONTACT_ID) violated - parent key not found
                                > >
                                > > I am in theprocess of trying to debug this but thought you might
                                > be
                                > > able to spot something wrong easily, no probs if not I will work
                                > on
                                > > this some more tonight.
                                > >
                                > >
                                > > Cheers,
                                > >
                                > >
                                > >
                                > > Scott.
                              • Ronald Bourret
                                Scott -- I ve run out of time to handle this right now (haven t even completely read your last two messages), so it ll have to wait until I m back from
                                Message 15 of 17 , Aug 17, 2005
                                • 0 Attachment
                                  Scott --

                                  I've run out of time to handle this right now (haven't even completely
                                  read your last two messages), so it'll have to wait until I'm back from
                                  vacation on 31 August. (Hopefully, you'll have figured it out by then :)

                                  A few comments:

                                  1) You're probably better off figuring out your relational model first
                                  and then doing the XML mapping. It's confusing to do both at the same time.

                                  2) You're correct about needing two join tables (contact <-> addresses
                                  and work <-> addresses). I missed that. Note that the "wrapper" elements
                                  will need different names, since each will map to a different join table
                                  and XML-DBMS does not allow you to map the same element to two different
                                  tables. For example, you could call one contactAddresses and the other
                                  workAddresses.

                                  3) You're also correct that you can't use the contact ID to join the
                                  work to the addresses. In fact, each company (work detail) should have
                                  its own ID and this is what you would use in the work <-> addresses join
                                  table. It's also needed because multiple people can share the same work
                                  details.

                                  Good luck,

                                  -- Ron
                                • sctt_bainbridge
                                  Hi Ron, Thanks for your help, this gives me a starting point, have a great break! Scott. ... completely ... from ... then :) ... first ... same time. ...
                                  Message 16 of 17 , Aug 17, 2005
                                  • 0 Attachment
                                    Hi Ron,

                                    Thanks for your help, this gives me a starting point, have a great
                                    break!



                                    Scott.
                                    --- In xml-dbms@yahoogroups.com, Ronald Bourret <rpbourret@r...>
                                    wrote:
                                    > Scott --
                                    >
                                    > I've run out of time to handle this right now (haven't even
                                    completely
                                    > read your last two messages), so it'll have to wait until I'm back
                                    from
                                    > vacation on 31 August. (Hopefully, you'll have figured it out by
                                    then :)
                                    >
                                    > A few comments:
                                    >
                                    > 1) You're probably better off figuring out your relational model
                                    first
                                    > and then doing the XML mapping. It's confusing to do both at the
                                    same time.
                                    >
                                    > 2) You're correct about needing two join tables (contact <->
                                    addresses
                                    > and work <-> addresses). I missed that. Note that the "wrapper"
                                    elements
                                    > will need different names, since each will map to a different join
                                    table
                                    > and XML-DBMS does not allow you to map the same element to two
                                    different
                                    > tables. For example, you could call one contactAddresses and the
                                    other
                                    > workAddresses.
                                    >
                                    > 3) You're also correct that you can't use the contact ID to join
                                    the
                                    > work to the addresses. In fact, each company (work detail) should
                                    have
                                    > its own ID and this is what you would use in the work <->
                                    addresses join
                                    > table. It's also needed because multiple people can share the same
                                    work
                                    > details.
                                    >
                                    > Good luck,
                                    >
                                    > -- Ron
                                  Your message has been successfully submitted and would be delivered to recipients shortly.