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

XML from Relational Tables

Expand Messages
  • ESN
    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,
    Message 1 of 2 , Oct 1, 2003
    • 0 Attachment
      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.

      :)
    • Ronald Bourret
      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
      Message 2 of 2 , 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.
      Your message has been successfully submitted and would be delivered to recipients shortly.