XML Object Generation from SQL Query

The following SQL Query generates a single XML Object Document which is having all the Department Details along with the corresponding Employee Deatails. 

1. SELECT XMLELEMENT("DEPARTMENTS",XMLAGG(XMLELEMENT("DEPARTMENT",XMLATTRIBUTES(deptno AS "ID",  
2.                                        DNAME AS "NAME"),  
3.                            XMLELEMENT("EMPLOYEES",(  
4.                            SELECT XMLAGG(XMLELEMENT("EMPLOYEE",  
5.                              XMLFOREST(EMPNO AS ID,ENAME AS EMP_NAME,  
6.                              JOB AS JOB, HIREDATE AS DOJ,  
7.                              SAL AS SALARY  
8.                              )))   
9.                              FROM emp e   
10.                              WHERE e.deptno = dept.deptno) )))) val  
11.                           FROM dept dept;  

Output 

1. <?xml version="1.0" encoding="UTF-8"?>  
2. <DEPARTMENTS>  
3.    <DEPARTMENT ID="10" NAME="ACCOUNTING">  
4.       <EMPLOYEES>  
5.          <EMPLOYEE>  
6.             <ID>7782</ID>  
7.             <EMP_NAME>CLARK</EMP_NAME>  
8.             <JOB>MANAGER</JOB>  
9.             <DOJ>1981-06-09</DOJ>  
10.             <SALARY>2450</SALARY>  
11.          </EMPLOYEE>  
12.          <EMPLOYEE>  
13.             <ID>7839</ID>  
14.             <EMP_NAME>KING</EMP_NAME>  
15.             <JOB>PRESIDENT</JOB>  
16.             <DOJ>1981-11-17</DOJ>  
17.             <SALARY>5000</SALARY>  
18.          </EMPLOYEE>  
19.          <EMPLOYEE>  
20.             <ID>7934</ID>  
21.             <EMP_NAME>MILLER</EMP_NAME>  
22.             <JOB>CLERK</JOB>  
23.             <DOJ>1982-01-23</DOJ>  
24.             <SALARY>1300</SALARY>  
25.          </EMPLOYEE>  
26.       </EMPLOYEES>  
27.    </DEPARTMENT>  
28.    <DEPARTMENT ID="20" NAME="RESEARCH">  
29.       <EMPLOYEES>  
30.          <EMPLOYEE>  
31.             <ID>7369</ID>  
32.             <EMP_NAME>SMITH</EMP_NAME>  
33.             <JOB>CLERK</JOB>  
34.             <DOJ>1980-12-17</DOJ>  
35.             <SALARY>800</SALARY>  
36.          </EMPLOYEE>  
37.          <EMPLOYEE>  
38.             <ID>7566</ID>  
39.             <EMP_NAME>JONES</EMP_NAME>  
40.             <JOB>MANAGER</JOB>  
41.             <DOJ>1981-04-02</DOJ>  
42.             <SALARY>2975</SALARY>  
43.          </EMPLOYEE>  
44.          <EMPLOYEE>  
45.             <ID>7788</ID>  
46.             <EMP_NAME>SCOTT</EMP_NAME>  
47.             <JOB>ANALYST</JOB>  
48.             <DOJ>1987-04-19</DOJ>  
49.             <SALARY>3000</SALARY>  
50.          </EMPLOYEE>  
51.          <EMPLOYEE>  
52.             <ID>7876</ID>  
53.             <EMP_NAME>ADAMS</EMP_NAME>  
54.             <JOB>CLERK</JOB>  
55.             <DOJ>1987-05-23</DOJ>  
56.             <SALARY>1100</SALARY>  
57.          </EMPLOYEE>  
58.          <EMPLOYEE>  
59.             <ID>7902</ID>  
60.             <EMP_NAME>FORD</EMP_NAME>  
61.             <JOB>ANALYST</JOB>  
62.             <DOJ>1981-12-03</DOJ>  
63.             <SALARY>3000</SALARY>  
64.          </EMPLOYEE>  
65.       </EMPLOYEES>  
66.    </DEPARTMENT>  
67.    <DEPARTMENT ID="30" NAME="SALES">  
68.       <EMPLOYEES>  
69.          <EMPLOYEE>  
70.             <ID>7499</ID>  
71.             <EMP_NAME>ALLEN</EMP_NAME>  
72.             <JOB>SALESMAN</JOB>  
73.             <DOJ>1981-02-20</DOJ>  
74.             <SALARY>1600</SALARY>  
75.          </EMPLOYEE>  
76.          <EMPLOYEE>  
77.             <ID>7521</ID>  
78.             <EMP_NAME>WARD</EMP_NAME>  
79.             <JOB>SALESMAN</JOB>  
80.             <DOJ>1981-02-22</DOJ>  
81.             <SALARY>1250</SALARY>  
82.          </EMPLOYEE>  
83.          <EMPLOYEE>  
84.             <ID>7654</ID>  
85.             <EMP_NAME>MARTIN</EMP_NAME>  
86.             <JOB>SALESMAN</JOB>  
87.             <DOJ>1981-09-28</DOJ>  
88.             <SALARY>1250</SALARY>  
89.          </EMPLOYEE>  
90.          <EMPLOYEE>  
91.             <ID>7698</ID>  
92.             <EMP_NAME>BLAKE</EMP_NAME>  
93.             <JOB>MANAGER</JOB>  
94.             <DOJ>1981-05-01</DOJ>  
95.             <SALARY>2850</SALARY>  
96.          </EMPLOYEE>  
97.          <EMPLOYEE>  
98.             <ID>7844</ID>  
99.             <EMP_NAME>TURNER</EMP_NAME>  
100.             <JOB>SALESMAN</JOB>  
101.             <DOJ>1981-09-08</DOJ>  
102.             <SALARY>1500</SALARY>  
103.          </EMPLOYEE>  
104.          <EMPLOYEE>  
105.             <ID>7900</ID>  
106.             <EMP_NAME>JAMES</EMP_NAME>  
107.             <JOB>CLERK</JOB>  
108.             <DOJ>1981-12-03</DOJ>  
109.             <SALARY>950</SALARY>  
110.          </EMPLOYEE>  
111.       </EMPLOYEES>  
112.    </DEPARTMENT>  
113.    <DEPARTMENT ID="40" NAME="OPERATIONS">  
114.       <EMPLOYEES />  
115.    </DEPARTMENT>  
116. </DEPARTMENTS>