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

3174Re: [xml-dbms] XML from Relational Tables

Expand Messages
  • Ronald Bourret
    Oct 7, 2003
    • 0 Attachment
      Sorry about the delay in answering. I've been very busy.

      1) The question you are asking is about how to use SQL/XML, which is a
      standard set of extensions to SQL for returning data as XML. It is
      supported by DB2 and Oracle -- not sure if any other databases are
      supporting it yet.

      2) This mailing list is for XML-DBMS (an admittedly poor name, but we're
      stuck with it), which is a set of Java packages for transferring data
      between XML documents and relational databases.

      3) I'm not a SQL/XML expert and don't have a system to test this query
      against, but you can try the following. Notice that I have used XMLAGG
      to retrieve all of the rows from Table 3 corresponding to a single row
      in Table 2. (The same is true of tables 3 and 4.)

      For clarity, I've written this as if you are retrieving data from tables
      A, B, C, and D. I've assumed you are retrieving one column from each
      table (A1, B1, C1, D1) and that tables are joined on ID columns (e.g.
      A.ID = B.A_ID). This will (hopefully) construct the following XML
      document:

      <A>
      <A1>...</A1>
      <B>
      <B1>...</B1>
      <C>
      <C1>...</C1>
      <D>...</D>
      <D>...</D>
      <D>...</D>
      </C>
      <C>
      ...
      </C>
      ...
      </B>
      </A>

      SELECT XMLELEMENT("A",
      XMLELEMENT("A1", A.A1),
      XMLELEMENT("B",
      XMLELEMENT("B1", B.B1),
      XMLAGG(XMLELEMENT("C",
      XMLAGG(XMLELEMENT("D",
      D.D1)
      )
      )
      )
      FROM A, B, C, D
      WHERE B.A_ID = A.ID AND
      C.B_ID = B.ID AND
      D.C_ID = C.ID

      -- Ron

      ESN wrote:
      >
      > I am trying to select data from relational tables and transform them
      > into XML format. What exactly I am trying to do is:
      >
      > - Select column values from Table 1, Table 2, Table 3, and Table 4
      > (which are all relational tables) (Select employees with assistants
      > showing the assistants' responsibilities and tasks)
      >
      > - Table 1 has one to one relationship with Table 2, Table 2 has one-
      > to-many relationship with Table 3, and Table 3 also has one-to-many
      > relationship with Table 4.
      >
      > My problem is:
      >
      > I can select data successfully from Table 1 and Table 2, however, I
      > couldn't select data from Table 3, and Table 4 because I don't know
      > how to construct WHERE condition.
      >
      > Here is my code for selecting data from Table 1 and Table 2:
      >
      > SELECT XMLELEMENT("Employee", XMLATTRIBUTES(e.empID, e.name),
      > XMLELEMENT("Assistant", XMLATTRIBUTES(e.assID,
      > (SELECT a.name FROM assistants a
      > WHERE a.assID = e.assID) as "Assistants")),
      > XMLELEMENT("Hiredate", e.hiredate)) AS "Result"
      > FROM employees e;
      >
      > I'd like to know how to select data in this structure: (Table 1 -
      > Employee, Table 2 - Assistant, Table 3 - Responsibility, Table 4 -
      > Task)
      >
      > < Employee >
      > < Assistant >
      > < Responsibiliy >
      > < Task > < / Task >
      > < Task > < / Task >
      > < / Responsibiliy >
      > < Responsibiliy >
      > < Task > < / Task >
      > < Task > < / Task >
      > < Task > < / Task >
      > < Task > < / Task >
      > < / Responsibiliy >
      > < / Assistant >
      > < / Employee >
      >
      > Any help regarding how to select data from more than 3 tables will be
      > greatly appreciated.
    • Show all 2 messages in this topic