Converting a Set of rows into a Single XML Object

In this blog, I have given an example which will generate a single XML object from a set of employee records. The query is written using XMLELEMENT and XMLAGG functions below. 

1. SELECT xmlelement("employeeDetails"  
2.                     ,  xmlagg( xmlelement("employeeDetail"  
3.                                 , xmlelement("employeeID",employee_id)  
4.                                 , xmlelement("firstName",first_name)  
5.                                 , xmlelement("lastName",last_name)  
6.                                 , xmlelement("email",email)  
7.                                 , xmlelement("phoneNumber",phone_number)  
8.                                 , xmlelement("hireDate",hire_date)  
9.                                 , xmlelement("salary",salary)  
10.                                 , xmlelement("departmentID",e.department_id)  
11.                                 , xmlelement("departmentName",department_name)  
12.                         )  
13.                       )  
14.                   ) AS employee_xml FROM employees e, departments d  
15.                     WHERE e.department_id=d.department_id  
16.                   ;  
17.   
18. Output  
19. -------  
20.   
21. <?xml version="1.0" encoding="UTF-8"?>  
22. <employeeDetails>  
23.    <employeeDetail>  
24.       <employeeID>200</employeeID>  
25.       <firstName>Jennifer</firstName>  
26.       <lastName>Whalen</lastName>  
27.       <email>JWHALEN</email>  
28.       <phoneNumber>515.123.4444</phoneNumber>  
29.       <hireDate>2003-09-17</hireDate>  
30.       <salary>4400</salary>  
31.       <departmentID>10</departmentID>  
32.       <departmentName>Administration</departmentName>  
33.    </employeeDetail>  
34.    <employeeDetail>  
35.       <employeeID>201</employeeID>  
36.       <firstName>Michael</firstName>  
37.       <lastName>Hartstein</lastName>  
38.       <email>MHARTSTE</email>  
39.       <phoneNumber>515.123.5555</phoneNumber>  
40.       <hireDate>2004-02-17</hireDate>  
41.       <salary>13000</salary>  
42.       <departmentID>20</departmentID>  
43.       <departmentName>Marketing</departmentName>  
44.    </employeeDetail>  
45.    <employeeDetail>  
46.       <employeeID>202</employeeID>  
47.       <firstName>Pat</firstName>  
48.       <lastName>Fay</lastName>  
49.       <email>PFAY</email>  
50.       <phoneNumber>603.123.6666</phoneNumber>  
51.       <hireDate>2005-08-17</hireDate>  
52.       <salary>6000</salary>  
53.       <departmentID>20</departmentID>  
54.       <departmentName>Marketing</departmentName>  
55.    </employeeDetail>  
56. </employeeDetails>  

The same code can be replaced with XMLELEMNT, XMLFOREST AND XMLAGG and the code is much reduced. 

1. SELECT xmlelement("employeeDetails"  
2.                     ,  xmlagg( xmlelement("employeeDetail"  
3.                                 , xmlforest(employee_id AS "employeeID"  
4.                                 , first_name AS "firstName"  
5.                                 , last_name AS "lastName"  
6.                                 , email AS"email"  
7.                                 , phone_number AS "phoneNumber"  
8.                                 , hire_date AS "hireDate"  
9.                                 , salary AS "salary"  
10.                                 , e.department_id AS "departmentID"  
11.                                 , department_name AS "departmentName"  
12.                         ))  
13.                       )  
14.                   ) AS employee_xml FROM employees e, departments d  
15.                     WHERE e.department_id=d.department_id  
16.                   ;   
17.   
18. Output  
19. -------  
20.   
21. <?xml version="1.0" encoding="UTF-8"?>  
22. <employeeDetails>  
23.    <employeeDetail>  
24.       <employeeID>200</employeeID>  
25.       <firstName>Jennifer</firstName>  
26.       <lastName>Whalen</lastName>  
27.       <email>JWHALEN</email>  
28.       <phoneNumber>515.123.4444</phoneNumber>  
29.       <hireDate>2003-09-17</hireDate>  
30.       <salary>4400</salary>  
31.       <departmentID>10</departmentID>  
32.       <departmentName>Administration</departmentName>  
33.    </employeeDetail>  
34.    <employeeDetail>  
35.       <employeeID>201</employeeID>  
36.       <firstName>Michael</firstName>  
37.       <lastName>Hartstein</lastName>  
38.       <email>MHARTSTE</email>  
39.       <phoneNumber>515.123.5555</phoneNumber>  
40.       <hireDate>2004-02-17</hireDate>  
41.       <salary>13000</salary>  
42.       <departmentID>20</departmentID>  
43.       <departmentName>Marketing</departmentName>  
44.    </employeeDetail>  
45.    <employeeDetail>  
46.       <employeeID>202</employeeID>  
47.       <firstName>Pat</firstName>  
48.       <lastName>Fay</lastName>  
49.       <email>PFAY</email>  
50.       <phoneNumber>603.123.6666</phoneNumber>  
51.       <hireDate>2005-08-17</hireDate>  
52.       <salary>6000</salary>  
53.       <departmentID>20</departmentID>  
54.       <departmentName>Marketing</departmentName>  
55.    </employeeDetail>  
56. </employeeDetails>
57.