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

Re: [xml-dbms] Need help on Mapping language.

Expand Messages
  • Ronald Bourret
    This is probably because the document shown in the FAQ isn t correct, as is noted in the IMPORTANT! paragraph. (When I discovered the error, I didn t have time
    Message 1 of 7 , Feb 27, 2007
    • 0 Attachment
      This is probably because the document shown in the FAQ isn't correct, as
      is noted in the IMPORTANT! paragraph. (When I discovered the error, I
      didn't have time to fix the FAQ.) The correct document is probably as is
      shown below. Let me know if you're still having trouble. (Your map and
      action files look correct, but I don't have time to test them right now.)

      -- Ron

      Corrected XML document. Note the additional Movies element around the
      second movie for actor 1.

      <Actors>
      <Actor ID="1">
      <Name>Al Pacino</Name>
      <Sex>M</Sex>
      <Movies>
      <Movie ID="1">
      <Title>Serpico</Title>
      <Rating>R</Rating>
      </Movie>
      </Movies>
      <Movies>
      <Movie ID="2">
      <Title>The Godfather</Title>
      <Rating>R</Rating>
      </Movie>
      </Movies>
      </Actor>
      <Actor ID="2">
      <Name>Marlon Brando</Name>
      <Sex>M</Sex>
      <Movies>
      <Movie ID="2">
      <Title>The Godfather</Title>
      <Rating>R</Rating>
      </Movie>
      </Movies>
      </Actor>
      <Actor ID="3">
      <Name>Jack Kehoe</Name>
      <Sex>M</Sex>
      <Movies>
      <Movie ID="1">
      <Title>Serpico</Title>
      <Rating>R</Rating>
      </Movie>
      </Movies>
      </Actor>
      </Actors>



      Janardhan, Jay wrote:

      > Thanks for your help Ron. I switched to V2.0. I can't say I still
      > understand the mapping language well enough!
      >
      >
      >
      >
      >
      > I was trying out the many-to-many relation example (actor/movie example
      > copy-paste from the FAQ page.) On MySQL, it somehow fails to insert the
      > second movie for actor ID=1. After updates this is how things look:
      >
      >
      >
      > mysql> select * from joinmoviesactors;
      >
      > +---------+---------+
      >
      > | actorID | movieID |
      >
      > +---------+---------+
      >
      > | 1 | 2 |
      >
      > | 2 | 2 |
      >
      > | 3 | 1 |
      >
      > +---------+---------+
      >
      >
      >
      > Note: missing actorID=1 and movieID=1.
      >
      >
      >
      >
      >
      > My mapping file:
      >
      >
      >
      > ===================================================================
      >
      > <?xml version='1.0' ?>
      >
      >
      >
      > <XMLToDBMS Version="2.0"
      > xmlns="http://www.xmlmiddleware.org/xmldbms/v2">
      >
      > <Options>
      >
      > <SimpleDateFormat Pattern="MM/dd/yyyy"
      > DefaultForTypes="DATE" />
      >
      > </Options>
      >
      > <Databases>
      >
      > <Database Name="Default">
      >
      > <Catalog>
      >
      > <Schema>
      >
      > <Table Name="actors">
      >
      > <Column Name="id" DataType="INTEGER"
      > Nullable="No"/>
      >
      > <Column Name="name"
      > DataType="VARCHAR" Length="255" Nullable="Yes"/>
      >
      > <PrimaryKey>
      >
      > <UseColumn Name="id"/>
      >
      > </PrimaryKey>
      >
      > </Table>
      >
      >
      >
      >
      > <Table Name="movies">
      >
      > <Column Name="id" DataType="INTEGER"
      > Nullable="No"/>
      >
      > <Column Name="title"
      > DataType="VARCHAR" Length="255" Nullable="Yes"/>
      >
      > <PrimaryKey>
      >
      > <UseColumn Name="id"/>
      >
      > </PrimaryKey>
      >
      > </Table>
      >
      >
      >
      >
      > <Table Name="joinmoviesactors">
      >
      > <Column Name="actorID"
      > DataType="INTEGER" Nullable="No"/>
      >
      > <Column Name="movieID"
      > DataType="INTEGER" Nullable="No"/>
      >
      > <PrimaryKey>
      >
      > <UseColumn Name="actorID"/>
      >
      > <UseColumn Name="movieID"/>
      >
      > </PrimaryKey>
      >
      > <ForeignKey Name="actor_FK">
      >
      > <UseTable Name="actors"/>
      >
      > <UseUniqueKey
      > Name="PrimaryKey" />
      >
      > <UseColumn Name="actorID"/>
      >
      > </ForeignKey>
      >
      > <ForeignKey Name="movies_FK">
      >
      > <UseTable Name="movies"/>
      >
      > <UseUniqueKey
      > Name="PrimaryKey" />
      >
      > <UseColumn Name="movieID"/>
      >
      > </ForeignKey>
      >
      > </Table>
      >
      >
      >
      >
      > </Schema>
      >
      > </Catalog>
      >
      > </Database>
      >
      > </Databases>
      >
      >
      >
      > <Maps>
      >
      > <ClassMap>
      >
      > <ElementType Name="Actor"/>
      >
      > <ToClassTable Name="actors"/>
      >
      > <PropertyMap>
      >
      > <Attribute Name="ID"/>
      >
      > <ToColumn Name="id"/>
      >
      > </PropertyMap>
      >
      > <PropertyMap>
      >
      > <ElementType Name="Name"/>
      >
      > <ToColumn Name="name"/>
      >
      > </PropertyMap>
      >
      > <RelatedClass KeyInParentTable="Unique">
      >
      > <ElementType Name="Movies" />
      >
      > <UseUniqueKey Name="PrimaryKey" />
      >
      > <UseForeignKey Name="actor_FK"/>
      >
      > </RelatedClass>
      >
      > </ClassMap>
      >
      >
      >
      > <ClassMap>
      >
      > <ElementType Name="Movies"/>
      >
      > <ToClassTable Name="joinmoviesactors"/>
      >
      > <RelatedClass KeyInParentTable="Foreign">
      >
      > <ElementType Name="Movie" />
      >
      > <UseUniqueKey Name="PrimaryKey" />
      >
      > <UseForeignKey Name="movies_FK"/>
      >
      > </RelatedClass>
      >
      > </ClassMap>
      >
      >
      >
      > <ClassMap>
      >
      > <ElementType Name="Movie"/>
      >
      > <ToClassTable Name="movies"/>
      >
      > <PropertyMap>
      >
      > <Attribute Name="ID"/>
      >
      > <ToColumn Name="id"/>
      >
      > </PropertyMap>
      >
      > <PropertyMap>
      >
      > <ElementType Name="Title"/>
      >
      > <ToColumn Name="title"/>
      >
      > </PropertyMap>
      >
      > </ClassMap>
      >
      >
      >
      > </Maps>
      >
      > </XMLToDBMS>
      >
      > ===================================================================
      >
      > Action file:
      >
      >
      >
      > <Actions Version="2.0"
      > xmlns="http://www.xmlmiddleware.org/xmldbms/actions/v2">
      >
      > <DefaultAction>
      >
      > <Insert />
      >
      > </DefaultAction>
      >
      > <Action>
      >
      > <ElementType Name="Movie"/>
      >
      > <SoftInsert/>
      >
      > </Action>
      >
      > </Actions>
      >
      >
      >
      >
      >
      >
      >
      > Thanks,
      >
      > -Jay
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      > ________________________________
      >
      > From: xml-dbms@yahoogroups.com [mailto:xml-dbms@yahoogroups.com] On
      > Behalf Of Ronald Bourret
      > Sent: Sunday, February 18, 2007 4:29 PM
      > To: xml-dbms@yahoogroups.com
      > Subject: Re: [xml-dbms] Need help on Mapping language.
      >
      >
      >
      > No. To do this, you would need to use XSLT to transform the document
      > first. You would then map the transformed document to the database. In
      > your case, you would need to transform the document to the following:
      >
      > <results>
      > <TableOne>
      > <id> 1 </id>
      > <data/>
      > </TableOne>
      > <TableTwo>
      > <id> 1 </id>
      > <data/>
      > </TableTwo>
      > </results>
      >
      > Using XSLT with XML-DBMS is a fairly common occurrence. As a general
      > rule, the structure of the XML document needs to "match" the structure
      > of the database schema.
      >
      > Note that you are also trying to do something else that is not supported
      >
      > by XML-DBMS. In particular, the id element is a sibling of the table
      > elements. Elements mapped to columns must be children (or in some cases,
      >
      > descendants) of elements mapped to tables.
      >
      > -- Ron
      >
      > P.S. I believe some commercial software, such as HiT Allora, can do what
      >
      > you ask for.
      >
      > Janardhan, Jay wrote:
      >
      >
      >>Thanks for the reply Ron.
      >>
      >>
      >>
      >>I'm switching to Java V2.0. While I start experimenting with V2.0, I
      >>have a quick question. Is it possible to map an element from the XML
      >>file to columns in different tables?
      >>
      >>
      >>
      >>For example,
      >>
      >>
      >>
      >><results>
      >>
      >><id> 1 </id>
      >>
      >><TableOne>
      >>
      >><data/>
      >>
      >></TableOne>
      >>
      >><TableTwo>
      >>
      >><data/>
      >>
      >></TableTwo>
      >>
      >></results>
      >>
      >>
      >>
      >>Table: TableOne
      >>
      >>ID NOT NULL,
      >>
      >>Column1
      >>
      >>Coulmn2
      >>
      >>
      >>
      >>Table: TableTwo
      >>
      >>ID NOT NULL,
      >>
      >>Column1
      >>
      >>
      >>
      >>In this example, is it possible to map <id> (from the XML file) to
      >>TableOne::ID and TableTwo::ID?
      >>
      >>Appreciate your assistance.
      >>
      >>Thanks,
      >>
      >>-Jay
      >
      >
      >
      >
      >
      >
      > [Non-text portions of this message have been removed]
      >
      >
      >
      >
      > 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
      >
      >
      >
      >
      >
    Your message has been successfully submitted and would be delivered to recipients shortly.