Relational data as XML

A few things to consider:

• Provide a namespace (see line 2). It will save you from a lot of troubles later on.
• Provide non-empty keys (see lines 3, 27 & 28). In an integration scenario, you will want to provide unique invariable integration keys, possibly a GUID.
• Never include an empty xsd:date tag (see line 30).

Queries:

-- 1 - All departments in a single document
select xmlelement("DEPARTMENTS",xmlattributes('http://http://dataflows.blogspot.com/SCOTT/depemp/1' as "xmlns")
                               ,xmlagg(xmlelement("DEPARTMENT",xmlattributes('DEP_'||to_char(dep.deptno) as "id")
                                                              ,xmlelement("NAME",dep.dname)
                                                              ,xmlelement("LOCATION",dep.loc)
                                                              ,xmlelement("EMPLOYEES",(select xmlagg(xmlelement("EMPLOYEE",xmlattributes('EMP_'||empno as "id"
                                                                                                                                        ,decode (mgr,null,'EMP_'||empno,'EMP_'||mgr) as "manager_id")
                                                                                                                          ,xmlelement("NAME",emp.ename)
                                                                                                                          ,case when emp.hiredate is null then null else xmlelement("HIRE_DATE",emp.hiredate) end case
                                                                                                                          ,xmlelement("JOB",emp.job)
                                                                                                                          ,xmlelement("SALARY",emp.sal)
                                                                                                                          ) order by emp.ename asc
                                                                                                               )
                                                                                       from emp emp
                                                                                       where dep.deptno=emp.deptno)
                                                                         )
                                                 ) order by dep.dname asc
                                      )) depemp
from dept dep
;

-- 2 - A document per department
select xmlelement("DEPARTMENT",xmlattributes('http://http://dataflows.blogspot.com/SCOTT/depemp/1' as "xmlns"
                                            ,'DEP_'||to_char(dep.deptno) as "id")
                              ,xmlelement("NAME",dep.dname)
                              ,xmlelement("LOCATION",dep.loc)
                              ,xmlelement("EMPLOYEES",(select xmlagg(xmlelement("EMPLOYEE",xmlattributes('EMP_'||empno as "id"
                                                                                                        ,decode (mgr,null,'EMP_'||empno,'EMP_'||mgr) as "manager_id")
                                                                                          ,xmlelement("NAME",emp.ename)
                                                                                          ,case when emp.hiredate is null then null else xmlelement("HIRE_DATE",emp.hiredate) end case
                                                                                          ,xmlelement("JOB",emp.job)
                                                                                          ,xmlelement("SALARY",emp.sal)
                                                                                          ) order by emp.ename asc
                                                                               )
                                                       from emp emp
                                                       where dep.deptno=emp.deptno)
                                         )
                 ) depemp
from dept dep
order by dep.dname asc
;

Results:

<-- data-blogger-escaped---="--" data-blogger-escaped--="-" data-blogger-escaped-1="1" data-blogger-escaped-a="a" data-blogger-escaped-department="department" data-blogger-escaped-document="document" data-blogger-escaped-per="per">

  
    ACCOUNTING
    NEW YORK
    
      
        CLARK
        1981-06-09
        MANAGER
        2450
      
      
        KING
        1981-11-17
        PRESIDENT
        5000
      
      
        MILLER
        1982-01-23
        CLERK
        1300
      
    
  
  
    OPERATIONS
    BOSTON
    
  
  
    RESEARCH
    DALLAS
    
      
        ADAMS
        1987-05-23
        CLERK
        1100
      
      
        FORD
        1981-12-03
        ANALYST
        3000
      
      
        JONES
        1981-04-02
        MANAGER
        2975
      
      
        SCOTT
        1987-04-19
        ANALYST
        3000
      
      
        SMITH
        1980-12-17
        CLERK
        800
      
    
  
  
    SALES
    CHICAGO
    
      
        ALLEN
        1981-02-20
        SALESMAN
        1600
      
      
        BLAKE
        1981-05-01
        MANAGER
        2850
      
      
        JAMES
        1981-12-03
        CLERK
        950
      
      
        MARTIN
        1981-09-28
        SALESMAN
        1250
      
      
        TURNER
        1981-09-08
        SALESMAN
        1500
      
      
        WARD
        1981-02-22
        SALESMAN
        1250
      
    
  


<-- data-blogger-escaped---="--" data-blogger-escaped--="-" data-blogger-escaped-2="2" data-blogger-escaped-a="a" data-blogger-escaped-department="department" data-blogger-escaped-document="document" data-blogger-escaped-per="per">

  ACCOUNTING
  NEW YORK
  
    
      CLARK
      1981-06-09
      MANAGER
      2450
    
    
      KING
      1981-11-17
      PRESIDENT
      5000
    
    
      MILLER
      1982-01-23
      CLERK
      1300