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

3173XML from Relational Tables

Expand Messages
  • ESN
    Oct 1, 2003
      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