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

Storing two XML elements in the same table

Expand Messages
  • sctt_bainbridge
    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
    Message 1 of 17 , Jul 31, 2005
      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.


      Thanks,


      Scott.
    • Ronald Bourret
      Suppose you have an XML document that looks like this: Sam Smith English 123 Main
      Message 2 of 17 , Aug 3, 2005
        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 3 of 17 , Aug 7, 2005
          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 4 of 17 , Aug 9, 2005
            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 5 of 17 , Aug 11, 2005
              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 6 of 17 , Aug 12, 2005
                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 7 of 17 , Aug 12, 2005
                  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 8 of 17 , Aug 13, 2005
                    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 9 of 17 , Aug 14, 2005
                      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 10 of 17 , Aug 14, 2005
                        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 11 of 17 , Aug 15, 2005
                          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 12 of 17 , Aug 15, 2005
                            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 13 of 17 , Aug 15, 2005
                              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 14 of 17 , Aug 16, 2005
                                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 15 of 17 , Aug 16, 2005
                                  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 16 of 17 , Aug 17, 2005
                                    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 17 of 17 , Aug 17, 2005
                                      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.