XMLAgg Function
XMLAgg() function is an aggregate function that produces a forest of XML elements from a collection of XML elements. Figure 10-7 describes the XMLAgg() syntax. As with XMLConcat(), any arguments that are null are dropped from the result. XMLAgg() function is similar to the SYS_XMLAGG() function except that it returns a forest of nodes, and does not take the XMLFormat() parameter. This function can be used to concatenate XMLType instances across multiple rows.
Figure 10-7 XMLAgg() Syntax
XMLAGG
Example 10-13 XMLAgg(): Generating Department Elements with a List of Employee Elements
This example produces department elements, with the list of employees belonging to that department:
SELECT XMLELEMENT( "Department", XMLATTRIBUTES ( e.dept AS "name" ), XMLAGG (XMLELEMENT ("emp", e.lname))) AS "dept_list" FROM employees e GROUP BY dept ;
This query might produce the following XML result:
<Department name="Accounting"> <emp>Yates</emp> <emp>Smith</emp> </Department>
<Department name="Shipping"> <emp>Oppenheimer</emp> <emp>Martin</emp> </Department>
Example 10-14 XMLAgg(): Generating Department Elements, Employee Elements Per Department, and Employee Dependents
XMLAgg() can be used to reflect the hierarchical nature of some relationships that exist in tables. The following example generates a department element for each department. Within this it creates elements for all employees of the department. Within each employee, it lists their dependents:
SELECT XMLELEMENT( "Department", XMLATTRIBUTES ( d.dname AS "name" ),
(SELECT XMLAGG(XMLELEMENT ("emp", XMLATTRIBUTES (e.ename AS name), ( SELECT XMLAGG(XMLELEMENT( "dependent",
XMLATTRIBUTES(de.name AS "name"))) FROM dependents de WHERE de.empno = e.empno ) ))
FROM emp e
WHERE e.deptno = d.deptno) ) AS "dept_list" FROM dept d ;
The query might produce a row containing the XMLType instance for each department.
<Department name="Accounting"> <emp name="Smith">
<dependent name="Sara Smith"/d> <dependent name="Joyce Smith"/> </emp>
<emp name="Yates"/> </Department>
<Department name="Shipping"> <emp name="Martin">
<dependent name="Alan Martin"/> </emp>
<emp name="Oppenheimer">
<dependent name="Ellen Cppenheimer"/> </emp> </Department>
Post a comment