XMLForest SQL Function

How to use XMLForest?

SQL function XMLForest produces a forest of XML elements from its arguments, which are expressions to be evaluated, with optional aliases.

Example 1:
 Generating Elements with Attribute and Child Elements
This example generates an Emp element for each employee, with a name attribute and elements with the employee hire date and department as the content.
SELECT XMLElement("Emp",
                  XMLAttributes(e.first_name ||' '|| e.last_name AS "name"),
                  XMLForest(e.hire_date, e.department AS "department"))
AS "RESULT"
FROM employees e WHERE e.department_id = 20;


Example 2:
Generating an Element from a User-Defined Datatype Instance
You can also use SQL function XMLForest to generate hierarchical XML from user-defined datatype instances.
SELECT XMLForest(
  dept_t(department_id,
         department_name,
         CAST (MULTISET (SELECT employee_id, last_name
                           FROM hr.employees e
                           WHERE e.department_id = d.department_id)
               AS emplist_t))
         AS "Department")
  AS deptxml
  FROM hr.departments d
  WHERE department_id=10;