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

Re: A couple of quick questions

Expand Messages
  • thetriviaboy
    Many thanks for your help, I ve now got it working with the aid of XSLTs for the final step in adding constants etc. I now have a problem concerning importing
    Message 1 of 4 , Jul 5 7:43 AM
    • 0 Attachment
      Many thanks for your help, I've now got it working with the aid of
      XSLTs for the final step in adding constants etc.

      I now have a problem concerning importing data from XML to the DBMS.

      I have two tables:

      Books - (ID (PK), AuthorID, Title (Unique) ) and
      Authors - (ID (PK), Name (Unique) )

      which are linked via the Books.AuthorID and Authors.ID field in a
      one-to-one relationship. This link is established in the map file and
      exports data like this:

      <book>
      <title>Lord of the Rings</title>
      <author>
      <name>J R R Tolkein</name>
      </author>
      </book>


      I have a situation whereby a user can create some xml in the above
      format and needs to be able to import it into the database. The user
      should have no knowledge of primary keys, or whether the record
      already exists in the database.

      What do I need to do in terms of actions etc to get XML-DBMS to insert
      a new record in the authors table based on whether there's a match on
      the name tag/field.

      Many thanks again,

      Andrew
    • Ronald Bourret
      Sorry about the late answer. I don t have time to test this right now, but either of the following should work: 1) a) Declare the primary key of the Authors
      Message 2 of 4 , Jul 14 11:59 PM
      • 0 Attachment
        Sorry about the late answer.

        I don't have time to test this right now, but either of the following
        should work:

        1) a) Declare the primary key of the Authors table to be Name. This
        means that the uniqueness of rows will be based on the Name field rather
        than the Authors.ID field. Note that XML-DBMS does not care about the
        actual key structures used by the database -- it simply pretends you
        have given it the correct information and acts accordingly.

        b) Set the action for the element corresponding to the Authors table to
        be UpdateOrInsert. This will attempt to update the record based on the
        primary key (which you have lied about and said is Name); if this fails,
        it assumes the row doesn't exist and inserts the row.

        2) Correctly declare the primary key of the Authors table to be ID and
        set the action to SoftInsert. This will attempt to insert the row and
        ignore any insert errors. Because of the uniqueness constraint on Name
        (this must exist in the database for this strategy to work), you will
        get an insert error if an author of the same name is already in the table.

        In summary, use (1) if you want to update existing rows and otherwiser
        insert. Use (2) if you just want to insert new rows.

        -- Ron

        thetriviaboy wrote:

        > Many thanks for your help, I've now got it working with the aid of
        > XSLTs for the final step in adding constants etc.
        >
        > I now have a problem concerning importing data from XML to the DBMS.
        >
        > I have two tables:
        >
        > Books - (ID (PK), AuthorID, Title (Unique) ) and
        > Authors - (ID (PK), Name (Unique) )
        >
        > which are linked via the Books.AuthorID and Authors.ID field in a
        > one-to-one relationship. This link is established in the map file and
        > exports data like this:
        >
        > <book>
        > <title>Lord of the Rings</title>
        > <author>
        > <name>J R R Tolkein</name>
        > </author>
        > </book>
        >
        >
        > I have a situation whereby a user can create some xml in the above
        > format and needs to be able to import it into the database. The user
        > should have no knowledge of primary keys, or whether the record
        > already exists in the database.
        >
        > What do I need to do in terms of actions etc to get XML-DBMS to insert
        > a new record in the authors table based on whether there's a match on
        > the name tag/field.
        >
        > Many thanks again,
        >
        > Andrew
      Your message has been successfully submitted and would be delivered to recipients shortly.