Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81

    Unanswered: nested XML queries

    Hi,

    I have 2 tables:

    create table DEPARTMENTS (deptid number, deptname varchar2(10))
    create table EMPLOYEES (empid number, deptid number, name varchar2(100), position varchar2(10) )

    How can I get the following XML as a result of joining these tables ?

    <DEPARTMENT name="ACCT">
    <EMPLOYEE name="Smith" position="Accountant" />
    <EMPLOYEE name="Pearks" position="Secretary" />
    </DEPARTMENT>
    <DEPARTMENT name="R&D">
    <EMPLOYEE name="Lall" position="DBA" />
    <EMPLOYEE name="Castona" position="Project Manager" />
    </DEPARTMENT>



    Thanks in advance
    --
    kukuk

  2. #2
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Code:
    SQL> select * from employees;
    
         EMPID     DEPTID NAME                           POSITION
    ---------- ---------- ------------------------------ ----------
             1          1 Smith                          Accountant
             2          1 Pearks                         Secretary
             3          2 Lall                              DBA
             4          2 Castona                       Proj Mgr
    
    SQL> select * from departments
      2  ;
    
        DEPTID DEPTNAME
    ---------- ----------
             1 ACCT
             2 RnD
    
    SQL> select d.deptname , substr(e.name,1,20) , e.position
      2  from departments d, employees e
      3  where e.deptid = d.deptid;
    
    DEPTNAME   SUBSTR(E.NAME,1,20)  POSITION
    ---------- -------------------- ----------
    ACCT       Smith                Accountant
    ACCT       Pearks               Secretary
    RnD         Lall                    DBA
    RnD         Castona             Proj Mgr
    
    SQL> break on deptname;
    SQL> select d.deptname , substr(e.name,1,20) , e.position
      2  from departments d, employees e
      3  where e.deptid = d.deptid;
    
    DEPTNAME   SUBSTR(E.NAME,1,20)  POSITION
    ---------- -------------------- ----------
    ACCT       Smith                Accountant
                   Pearks               Secretary
    RnD         Lall                    DBA
                   Castona             Proj Mgr
    Oracle can do wonders !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •