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

Re: [xml-dbms] Result Set problem

Expand Messages
  • Ronald Bourret
    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
    Message 1 of 8 , Apr 1, 2003
    • 0 Attachment
      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>

      3) The third way to retrieve data from multiple tables is to pass
      multiple table names and keys to DOMToDBMS.retrieveData. In this case,
      no related class elements are needed, but tables must be mapped as root
      tables or pseudo-root tables:

      <IgnoreRoot>
      <ElementType Name="A_B_Data"/>
      <PseudoRoot>
      <ElementType Name="A"/>
      <CandidateKey Generate="No">
      <Column Name="a_pk"/>
      </CandidateKey>
      </PseudoRoot>
      <PseudoRoot>
      <ElementType Name="B"/>
      <CandidateKey Generate="No">
      <Column Name="b_pk"/>
      </CandidateKey>
      </PseudoRoot>
      </IgnoreRoot>
      <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>
      </ClassMap>
      <ClassMap>
      <ElementType Name="B" />
      <ToClassTable>
      <Table Name="B" />
      </ToClassTable>
      <PropertyMap>
      <ElementType Name="b_pk" />
      <ToColumn>
      <Column Name="b_pk" />
      </ToColumn>
      </PropertyMap>
      <PropertyMap>
      <ElementType Name="b_data" />
      <ToColumn>
      <Column Name="b_data" />
      </ToColumn>
      </PropertyMap>
      </ClassMap>

      For example, the following code returns data for the row in A with
      primary key 1 and the rows in B with primary keys 2, 3, and 4.
      (Hopefully I've done the array initialization correctly.)

      DBMSToDOM dbmsToDOM = new DBMSToDOM(map, new DF_Oracle2());
      String[] tableNames = {"A", "B", "B", "B"};
      String[][] keys = {{1}, {2}, {3}, {4}};
      Document doc = dbmsToDOM.retrieveDocument(tableNames, keys);

      Using the above map, this returns the following XML:
      assuming that the rows in B from the previous example have key values,
      then the

      <A_B_Data>
      <A>
      <a_pk>1</a_pk>
      <a_data>a1</a_data>
      </A>
      <B>
      <b_pk>2</b_pk>
      <b_data>b1</b_data>
      </B>
      <B>
      <b_pk>3</b_pk>
      <b_data>b2</b_data>
      </B>
      <B>
      <b_pk>4</b_pk>
      <b_data>b3</b_data>
      </B>
      </A_B_Data>

      Again, if more than one row is retrieved, there must be an element type
      mapped as an ignored root. Because this method requires the application
      to know the key values, it is not as good a choice if the data in the
      second table is related to the data in the first table. This is because
      such data is related with foreign keys, but the code requires you to use
      primary keys. (Of course, you could lie in your map and tell the code
      that the foreign key columns are the primary key columns :) It is better
      for bulk transfer of data from multiple tables.

      -- Ron

      rene adam wrote:
      >
      > I have more than one tables in my db. So i think the
      > first solution does not work. I show you only a part
      > of my map file, not all.
      >
      > I use a select statement on many tables and i don't
      > know if i can do this by using only one classmap in my
      > map file. I don't know how to do to make it work. As
      > this work has been done by someone else, passing to v2
      > will be more work for me to understand how it works.
      > Can you help me? I send you my map file:
      > Here is my select statement:
      > SELECT "version", "IDMEF-Message"."AdditionalDataFK",
      > "IDMEF-MessageOrder",
      > "IDMEF-Message"."IDMEF-MessagePK", "Alert"."AlertPK",
      > "Alert"."i
      > mpact", "Alert"."IDMEF-MessageFK", "Alert"."ident",
      > "Alert"."AlertOrder", "Analyzer"."AnalyzerPK",
      > "Analyzer"."AnalyzerOrder", "Analyzer"."Ale
      > rtFK", "Analyzer"."HeartbeatFK",
      > "Analyzer"."analyzerid", "CreateTimePCDATAOrder",
      > "CreateTime"."CreateTimeOrder",
      > "CreateTime"."CreateTimePCD
      > ATA", "CreateTime"."AlertFK", "CreateTime"."ntpstamp",
      > "CreateTime"."HeartbeatFK",
      > "CreateTime"."CreateTimePK",
      > "DetectTime"."DetectTimeOrder"
      > , "DetectTime"."DetectTimePK",
      > "DetectTime"."DetectTimePCDATA",
      > "DetectTime"."AlertFK", "DetectTime"."ntpstamp",
      > "DetectTimePCDATAOrder", "Sou
      > rce"."SourcePK", "Source"."interface",
      > "Source"."ident", "Source"."AlertFK",
      > "Source"."SourceOrder", "Source"."spoofed",
      > "Target"."TargetOrder
      > ", "Target"."TargetPK", "Target"."interface",
      > "Target"."ident", "Target"."AlertFK",
      > "Target"."decoy", "Classification"."AlertFK",
      > "Classificat
      > ion"."ClassificationPK", "Classification"."origin",
      > "ClassificationOrder", "Process"."ident",
      > "Process"."SourceFK", "Process"."TargetFK", "Pro
      > cess"."AnalyzerFK", "Process"."ProcessOrder",
      > "Process"."ProcessPK", "Node"."NodePK",
      > "Node"."category", "Node"."NodeOrder", "Node"."ident",
      > "
      > Node"."SourceFK", "Node"."TargetFK",
      > "Node"."AnalyzerFK", "name1"."CorrelationAlertFK",
      > "name1"."ServiceFK", "name1"."UserIdFK", "name1"."Clas
      > sificationFK", "name1"."nameOrder",
      > "name1"."ProcessFK", "name1"."namePCDATA",
      > "name1"."NodeFK", "name1"."namePCDATAOrder",
      > "name1"."namePK",
      > "name1"."ToolAlertFK" FROM "IDMEF-Message", "Alert",
      > "Analyzer", "CreateTime", "DetectTime", "Source",
      > "Target", "Classification", "Process",
      > "Node", "name1" WHERE
      > "IDMEF-MessagePK"="Alert"."IDMEF-MessageFK" AND
      > "AlertPK"="Analyzer"."AlertFK" AND
      > "AlertPK"="DetectTime"."AlertFK" AND
      > "AlertPK"="CreateTime"."AlertFK" AND
      > "AlertPK"="Source"."AlertFK" AND
      > "AlertPK"="Target"."AlertFK" AND
      > "AlertPK"="Classification"."AlertFK" AN
      > D "AnalyzerPK"="Process"."AnalyzerFK" AND
      > "TargetPK"="Node"."TargetFK" AND
      > "ClassificationPK"="name1"."ClassificationFK"
    • rene adam
      When using the ResultSet method, i have such a message: Unable to retrieve the IDMEF document. de.tudarmstadt.ito.xmldbms.InvalidMapException: Element type
      Message 2 of 8 , Apr 3, 2003
      • 0 Attachment
        When using the ResultSet method, i have such a
        message:
        Unable to retrieve the IDMEF document.
        de.tudarmstadt.ito.xmldbms.InvalidMapException:
        Element type IDMEF-Message was listed as a related
        class but was never mapped.
        HERE IS MY ERROR STACK:
        at
        de.tudarmstadt.ito.xmldbms.mapfactories.TempMap.checkRelatedClasses(TempMap.java:343)
        at
        de.tudarmstadt.ito.xmldbms.mapfactories.TempMap.createTableMapsFromClassMaps(TempMap.java:319)
        at
        de.tudarmstadt.ito.xmldbms.mapfactories.MapFactory_MapDocument.createMap(MapFactory_MapDocument.java:336)
        at
        diams.dbClient.DBThreadRequest.createMap(DBThreadRequest.java:117)
        at
        diams.dbClient.DBThreadRequest.run(DBThreadRequest.java:190)

        but I don't use the "RelatedClass" tag. Do I write a
        "RelatedClassTag" for all the tables I use in my
        "Select query"?
        HERE IS MY SELECT QUERY: have you got any suggestion
        on how to use the resultSet method in my case? I join
        to this mail my map file.

        7=SELECT "IDMEF-Message"."version" as "version",
        "IDMEF-Message"."AdditionalDataFK" as
        "AdditionalDataFK", "IDMEF-MessageOrder" as
        "IDMEF-MessageOrder",
        "IDMEF-Message"."IDMEF-MessagePK" as
        "IDMEF-MessagePK", "Alert"."AlertPK" as "AlertPK",
        "Alert"."impact" as "impact",
        "Alert"."IDMEF-MessageFK" as "IDMEF-MessageFK",
        "Alert"."ident" as "AlertIdent", "Alert"."AlertOrder"
        AS "AlertOrder", "Analyzer"."AnalyzerPK" AS
        "AnalyzerPK", "Analyzer"."AnalyzerOrder" AS
        "AnalyzerOrder", "Analyzer"."AlertFK" AS
        "AnalyzerAlertFK" , "Analyzer"."HeartbeatFK" AS
        "AnalyzerHeartbeatFK" , "Analyzer"."analyzerid" AS
        "analyzerid" , "CreateTime"."CreateTimePCDATAOrder" AS
        "CreateTimePCDATAOrder" ,
        "CreateTime"."CreateTimeOrder" AS "CreateTimeOrder" ,
        "CreateTime"."CreateTimePCDATA" AS "CreateTimePCDATA"
        , "CreateTime"."AlertFK" AS "CreateTimeAlertFK" ,
        "CreateTime"."ntpstamp" AS "CreateTimeNtpstamp" ,
        "CreateTime"."HeartbeatFK" AS "CreateTimeHeartbeatFK"
        , "CreateTime"."CreateTimePK" AS "CreateTimePK" ,
        "DetectTime"."DetectTimeOrder" AS "DetectTimeOrder" ,
        "DetectTime"."DetectTimePK" AS "DetectTimePK" ,
        "DetectTime"."DetectTimePCDATA" AS "DetectTimePCDATA"
        , "DetectTime"."AlertFK" AS "DetectTimeAlertFK" ,
        "DetectTime"."ntpstamp" AS "DetectTimeNtpstamp" ,
        "DetectTimePCDATAOrder" AS "DetectTimePCDATAOrder" ,
        "Source"."SourcePK" AS "SourcePK" ,
        "Source"."interface" AS "SourceInterface" ,
        "Source"."ident" AS "SourceIdent" , "Source"."AlertFK"
        AS "SourceAlertFK" , "Source"."SourceOrder" AS
        "SourceOrder" , "Source"."spoofed" AS "SourceSpoofed"
        , "Target"."TargetOrder" AS "TargetOrder" ,
        "Target"."TargetPK" AS "TargetPK" ,
        "Target"."interface" AS "TargetInterface" ,
        "Target"."ident" AS "TargetIdent" ,
        "Target"."AlertFK" AS "TargetAlertFK" ,
        "Target"."decoy" AS "TargetDecoy" ,
        "Classification"."AlertFK" AS "ClassificationAlertFK"
        , "Classification"."ClassificationPK" AS
        "ClassificationPK" , "Classification"."origin" AS
        "ClassificationOrigin" ,
        "Classification"."ClassificationOrder" AS
        "ClassificationOrder" , "Process"."ident" AS
        "ProcessIdent" , "Process"."SourceFK" AS
        "ProcessSourceFK" , "Process"."TargetFK" AS
        "ProcessTargetFK" , "Process"."AnalyzerFK" AS
        "ProcessAnalyzerFK" , "Process"."ProcessOrder" AS
        "ProcessOrder" , "Process"."ProcessPK" AS "ProcessPK"
        , "Node"."NodePK" AS "NodePK" , "Node"."category" AS
        "NodeCategory" , "Node"."NodeOrder" AS "NodeOrder" ,
        "Node"."ident" AS "NodeIdent" , "Node"."SourceFK" AS
        "NodeSourceFK" , "Node"."TargetFK" AS "NodeTargetFK" ,
        "Node"."AnalyzerFK" AS "NodeAnalyzerFK" ,
        "name1"."CorrelationAlertFK" AS
        "name1CorrelationAlertFK" , "name1"."ServiceFK" AS
        "name1ServiceFK" , "name1"."UserIdFK" AS
        "name1UserIdFK" , "name1"."ClassificationFK" AS
        "name1ClassificationFK" , "name1"."nameOrder" AS
        "nameOrder" , "name1"."ProcessFK" AS "name1ProcessFK"
        , "name1"."namePCDATA" AS "namePCDATA" ,
        "name1"."NodeFK" AS "name1NodeFK" ,
        "name1"."namePCDATAOrder" AS "namePCDATAOrder" ,
        "name1"."namePK" AS "namePK" , "name1"."ToolAlertFK"
        AS "name1ToolAlertFK" FROM "IDMEF-Message", "Alert",
        "Analyzer", "CreateTime", "DetectTime", "Source",
        "Target", "Classification", "Process", "Node", "name1"
        WHERE "IDMEF-MessagePK"="Alert"."IDMEF-MessageFK" AND
        "AlertPK"="Analyzer"."AlertFK" AND
        "AlertPK"="DetectTime"."AlertFK" AND
        "AlertPK"="CreateTime"."AlertFK" AND
        "AlertPK"="Source"."AlertFK" AND
        "AlertPK"="Target"."AlertFK" AND
        "AlertPK"="Classification"."AlertFK" AND
        "AnalyzerPK"="Process"."AnalyzerFK" AND
        "TargetPK"="Node"."TargetFK" AND
        "ClassificationPK"="name1"."ClassificationFK"
        #

        --- 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

        [Non-text portions of this message have been removed]
      • rene adam
        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
        Message 3 of 8 , Apr 4, 2003
        • 0 Attachment
          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
        • Ronald Bourret
          Hmmmm. The list does not accept attachments, so your map file was deleted. Could you please post it in the body of a message or send it to me privately?
          Message 4 of 8 , Apr 10, 2003
          • 0 Attachment
            Hmmmm. The list does not accept attachments, so your map file was
            deleted. Could you please post it in the body of a message or send it to
            me privately? Thanks.

            In answer to your question about related classes, you do not need to use
            related classes for tables in your SELECT statement. You only use
            related classes for table whose data you want nested inside the data for
            another table. For example, to get the following:

            <A>
            <A1>...</A1>
            <A2>...</A2>
            <B>
            <B1>...</B1>
            <B2>...</B2>
            </B>
            </A>

            you would map both A and B using class maps. You would also list B as a
            related class of A. That is, inside the ClassMap element for A, there
            would be a RelatedClass element for B. However, this does not appear to
            apply to you.

            -- Ron

            rene adam wrote:
            >
            > When using the ResultSet method, i have such a
            > message:
            > Unable to retrieve the IDMEF document.
            > de.tudarmstadt.ito.xmldbms.InvalidMapException:
            > Element type IDMEF-Message was listed as a related
            > class but was never mapped.
          • Ronald Bourret
            The sales_rs2.map in the samples directory shows how to write a map for a SELECT statement that joins data from two tables. Is there a reason you can t
            Message 5 of 8 , Apr 10, 2003
            • 0 Attachment
              The sales_rs2.map in the samples directory shows how to write a map for
              a SELECT statement that joins data from two tables. Is there a reason
              you can't generalize this to n tables?

              Also, can you explain why you are creating a result set over multiple
              tables instead of nesting the results of tables inside the results of
              other tables? Is the relationship between all the tables 1-to-1? (If the
              relationship between any of the tables is 1-to-many, returning all the
              data in a single result set means you will have duplicate data in your
              XML document.)

              -- Ron

              rene adam wrote:
              >
              > 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.
            Your message has been successfully submitted and would be delivered to recipients shortly.