oracle xml

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

출처 : http://srinisreeramoju.blogspot.kr/2010/03/oracle-generating-xml-using-xmlforest.html

예제 )

1.

WITH TEST AS (SELECT '<Data>
                        <Good>
                        <CallNumber>TES</CallNumber>
                        <TwitterUrl>TEST</TwitterUrl>
                        <FacebookUrl>TEST</FacebookUrl>
                        <EnableRemoteSvc>Y</EnableRemoteSvc>
                        </Good>
                     </Data>' ADD_INF6 FROM DUAL)
SELECT EXTRACTVALUE(XMLTYPE(NVL(ADD_INF6, '<Data/>')), 'Data/Good/CallNumber') AS CALLNO
  FROM TEST  

출력결과 : TES

2.
/* Formatted on 2013/07/04 오후 12:19:48 (QP5 v5.227.12220.39754) */
SELECT XMLELEMENT(
           "Data"
          ,XMLELEMENT(
               "Data2"
              ,XMLFOREST(NVL('test', '') AS "CallNumber"
                        ,NVL('testurl', '') AS "TwitterUrl"
                        ,NVL('testurl1', '') AS "FacebookUrl"
                        ,NVL('ok', '') AS "EnableRemoteSvc")
           )
       )
           AS TEST
  FROM DUAL                            

출력 결과 :  <Data><Data2><CallNumber>test</CallNumber><TwitterUrl>testurl</TwitterUrl><FacebookUrl>testurl1</FacebookUrl><EnableRemoteSvc>ok</EnableRemoteSvc></Data2></Data>