Oracle: Generating XML Using XMLFOREST

XMLForest produces forest of XML elements from its arguments, which are expressions to be evaluated, with optional aliases.
Each of the value expressions is converted to XML.

Syntax:      XMLFOREST ( value_expr AS c_alias)

To understand more about XMLForest in a clear way, Let us see one simple example to create your XML document using a SQL query that could give you some sense to it.

Following example creates a XML document per each employee record without using XMLFOREST:
NOTE: Casting to XMLTYPE is optional here:

SELECT XMLTYPE
 (XMLELEMENT ("ROOT",
         XMLELEMENT ("REC",
         XMLELEMENT ("EMPNO", empno),
         XMLELEMENT ("ENAME", ename),
         XMLELEMENT ("JOB", job)
             )
 ).getclobval () ) AS "RESULT"
FROM EMP
WHERE ROWNUM<3

Output:
Row1:

<ROOT>

<REC >

<EMPNO>7369</EMPNO>

<ENAME>SMITH</ENAME>

<JOB>CLERK</JOB>

</REC>

</ROOT>

Row2:

<ROOT>

<REC EMPNO="7499">

<EMPNO>7499</EMPNO>

<ENAME>ALLEN</ENAME>

<JOB>SALESMAN</JOB>

</REC>

</ROOT>

Achieve the same above result by simply using XMLFOREST: Instead of creating XMLELEMENT for each coulmn you could simply use XMLFOREST to do the job.

SELECT XMLElement("ROOT",
       XMLForest(empno, ename, job, mgr, hiredate, sal, comm, deptno)
   ) AS "RESULT"
FROM EMP 

Output:
Row1:

<ROOT>

<EMPNO>7369</EMPNO>

<ENAME>SMITH</ENAME>

<JOB>CLERK</JOB>

<MGR>7902</MGR>

<HIREDATE>1980-12-17</HIREDATE>

<SAL>800</SAL>

<DEPTNO>20</DEPTNO>

</ROOT>

Row2:

<ROOT>

<EMPNO>7499</EMPNO>

<ENAME>ALLEN</ENAME>

<JOB>SALESMAN</JOB>

<MGR>7698</MGR>

<HIREDATE>1981-02-20</HIREDATE>

<SAL>1600</SAL>

<COMM>300</COMM>

<DEPTNO>30</DEPTNO>

</ROOT>

An alternate Query by using column alias:
SELECT XMLELEMENT ("ROOT",
       XMLFOREST (empno AS "EMP",
                  ename AS "NAME",
                  job AS "JOB",
                  mgr AS "MANAGER",
                  hiredate AS "HIRE_DATE",
                  sal AS "SALARY",
                  comm AS "COMMISSION",
                  deptno AS "DEPR_NO"
                 ) ) AS "RESULT"
FROM emp
WHERE ROWNUM < 3