본문 바로가기

SQL

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>

 

'SQL' 카테고리의 다른 글

oracle xmltype  (0) 2013.07.04
oracle XMLELEMENT,XMLAGG,XMLFOREST,XMLATTRIBUTES 함수  (0) 2013.07.04
ORACLE 행을 열로 , 열을 행으로  (0) 2013.01.29
oracle exists vs in  (0) 2013.01.29
가로를 세로로  (0) 2013.01.24