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

2846Re: [xml-dbms] Result Set problem

Expand Messages
  • rene adam
    Apr 4 12:17 AM
      Hello, as ardavan-kanani as done, does someone has an
      complete example of (map file + sql file) in the case
      of transferring data from db to xml, using Result Set
      table, and having more than two tables? It would be so
      helpful for me an other people, i am sure.In my db i
      have more than 40 tables so if i have an example with
      two tables, it is helful, if my question is not clear,
      tell it to me and i will try to give you a short
      example.
      Thank you.

      --- Ronald Bourret <rpbourret@...> wrote:
      > Sorry I haven't answered sooner. I have been very
      > busy.
      >
      > In answer to your first two comments, you can use
      > the first option (even
      > though you have data from multiple tables) and you
      > only need one
      > classmap for a select statement.
      >
      > There are three ways to retrieve data from multiple
      > tables in XML-DBMS
      > v1.x. (Version 2.0 provides roughly similar
      > capabilities.) I will
      > illustrate with the following two tables:
      >
      > A: a_pk, a_data
      > B: b_pk, a_fk, b_data
      >
      > 1) Create a result set from both tables and use a
      > class map for this
      > result set that uses the special table name "Result
      > Set". For example,
      > use the following SELECT statement to retrieve the
      > data for the rows
      > corresponding to a primary key of 1 in table A:
      >
      > SELECT "a_data", "b_data" FROM A, B
      > WHERE "a_pk"=1 AND "b_fk"="a_pk"
      >
      > and use the following class map for this result set:
      >
      > <ClassMap>
      > <ElementType Name="ResultSet" />
      > <ToClassTable>
      > <Table Name="Result Set" />
      > </ToClassTable>
      > <PropertyMap>
      > <ElementType Name="a_pk" />
      > <ToColumn>
      > <Column Name="a_pk" />
      > </ToColumn>
      > </PropertyMap>
      > <PropertyMap>
      > <ElementType Name="a_data" />
      > <ToColumn>
      > <Column Name="a_data" />
      > </ToColumn>
      > </PropertyMap>
      > <PropertyMap>
      > <ElementType Name="b_data" />
      > <ToColumn>
      > <Column Name="b_data" />
      > </ToColumn>
      > </PropertyMap>
      > </ClassMap>
      >
      > A couple of things to note here:
      >
      > a) It is not always clear what element names to use.
      > For example, in the
      > following SELECT statement, I am not sure what names
      > will be returned by
      > JDBC's ResultSetMetaData:
      >
      > SELECT TABLE1.COLUMN1, TABLE2.COLUMN1
      > FROM TABLE1, TABLE2
      > WHERE TABLE2.COLUMN2 = TABLE1.COLUMN2
      >
      > If you have problems where data isn't being
      > transferred, then use column
      > aliases in the select statement and as column names
      > in your map. For
      > example, you could use the following select
      > statement instead of the
      > above select statement:
      >
      > SELECT TABLE1.COLUMN1 AS T1C1, TABLE2.COLUMN1 AS
      > T2C2
      > FROM TABLE1, TABLE2
      > WHERE TABLE2.COLUMN2 = TABLE1.COLUMN2
      >
      > and use the column names T1C1 and T2C2 in your map
      > file.
      >
      > b) XML-DBMS is fairly liberal with respect to
      > ignoring extra columns or
      > properties. For example, if a column is in the
      > result set but is not
      > mapped, it is ignored. Similarly, if a property
      > (element type, attribute
      > or PCDATA) is in the map file but does not have a
      > corresponding column
      > in the result set, it is ignored. This means that a
      > single class map
      > element can be used to map a variety of result sets.
      >
      > c) If the result set has more than one row in it,
      > there must be an
      > element type mapped as an ignored root. Otherwise,
      > the code will try to
      > add more than one (row) element to the document node
      > and the DOM code
      > will throw an exception.
      >
      > The major drawback to this method is that data from
      > child tables is not
      > nested inside data from parent tables. For example,
      > if there are
      > multiple rows in B for each row in A, the data for A
      > will be repeated
      > for each row in B. For example, if there are three
      > rows in B (data
      > values b1, b2, and b3) corresponding to the row in A
      > with primary key 1,
      > the following XML will be generated. (This assumes
      > an ignored root
      > element type of Result.)
      >
      > <Result>
      > <ResultSet>
      > <a_pk>1</a_pk>
      > <a_data>a1</a_data>
      > <b_data>b1</b_data>
      > </ResultSet>
      > <ResultSet>
      > <a_pk>1</a_pk>
      > <a_data>a1</a_data>
      > <b_data>b2</b_data>
      > </ResultSet>
      > <ResultSet>
      > <a_pk>1</a_pk>
      > <a_data>a1</a_data>
      > <b_data>b3</b_data>
      > </ResultSet>
      > </Result>
      >
      > 2) The second way to return data from two related
      > tables is to map one
      > table as a related class of the other. For example:
      >
      > <ClassMap>
      > <ElementType Name="A" />
      > <ToClassTable>
      > <Table Name="A" />
      > </ToClassTable>
      > <PropertyMap>
      > <ElementType Name="a_pk" />
      > <ToColumn>
      > <Column Name="a_pk" />
      > </ToColumn>
      > </PropertyMap>
      > <PropertyMap>
      > <ElementType Name="a_data" />
      > <ToColumn>
      > <Column Name="a_data" />
      > </ToColumn>
      > </PropertyMap>
      > <RelatedClass KeyInParentTable="Candidate">
      > <ElementType Name="B"/>
      > <CandidateKey Generate="No">
      > <Column Name="a_pk"/>
      > </CandidateKey>
      > <ForeignKey>
      > <Column Name="b_fk"/>
      > </ForeignKey>
      > </RelatedClass>
      > </ClassMap>
      > <ClassMap>
      > <ElementType Name="B" />
      > <ToClassTable>
      > <Table Name="B" />
      > </ToClassTable>
      > <PropertyMap>
      > <ElementType Name="b_data" />
      > <ToColumn>
      > <Column Name="b_data" />
      > </ToColumn>
      > </PropertyMap>
      > </ClassMap>
      >
      > The main advantage of this method is that the data
      > for the child table
      > is nested inside the data for the parent table. This
      > is the most common
      > way to return data from two tables. For example, the
      > above data would
      > be:
      >
      > <A>
      > <a_pk>1</a_pk>
      > <a_data>a1</a_data>
      > <B>
      > <b_data>b1</b_data>
      > </B>
      > <B>
      > <b_data>b2</b_data>
      > </B>
      > <B>
      > <b_data>b3</b_data>
      > </B>
      > </A>
      >
      === message truncated ===


      __________________________________________________
      Do you Yahoo!?
      Yahoo! Tax Center - File online, calculators, forms, and more
      http://tax.yahoo.com
    • Show all 8 messages in this topic