[Oracle] XMLELEMENT 예제 및 설명

위에서 XMLELEMENT(xml구분<></>, 구분자)
XMLAGG (ORDER BY XX) 무엇을 기준으로 정렬
getStringVar는 Text Value를 String 으로 변환해주는 역할

 

 

==> XMLELEMENT 예제

WITH T AS
    (
    SELECT 1 AS COL1, 'A' COL2 FROM DUAL UNION ALL
    SELECT 1 AS COL1, 'B' COL2 FROM DUAL UNION ALL
    SELECT 1 AS COL1, 'C' COL2 FROM DUAL
    )
SELECT COL1
     , XMLAGG(XMLELEMENT("NM", COL2, '^') ORDER BY ROWNUM).EXTRACT('//text()').GETSTRINGVAL() AS COL2
     , XMLAGG(XMLELEMENT("NM", '^', COL2) ORDER BY ROWNUM).EXTRACT('//text()').GETSTRINGVAL() AS COL3
  FROM T
 GROUP BY COL1

결과)
 
1 1 A^B^C^ ^A^B^C

 

==> 다른 XMLELEMENT 예제
WITH TAB AS
(
SELECT 'X' GBN,'A01' ITEM_CD FROM DUAL UNION ALL
SELECT 'X' GBN,'A02' ITEM_CD FROM DUAL UNION ALL
SELECT 'X' GBN,'B02' ITEM_CD FROM DUAL UNION ALL
SELECT 'Y' GBN,'B01' ITEM_CD FROM DUAL UNION ALL
SELECT 'Y' GBN,'B02' ITEM_CD FROM DUAL UNION ALL
SELECT 'Z' GBN,'G01' ITEM_CD FROM DUAL UNION ALL
SELECT 'Z' GBN,'A02' ITEM_CD FROM DUAL UNION ALL
SELECT 'Z' GBN,'B02' ITEM_CD FROM DUAL
)
/*select gbn, max(decode(rn,1,item_cd)) || max(decode(rn,2,item_cd)) || max(decode(rn,3,item_cd)) as item_cd
  from (SELECT tab.*, row_number() OVER(PARTITION BY gbn ORDER BY ROWNUM) AS rn
          FROM tab)
    group by gbn*/

SELECT   GBN
       --, xmlagg(XMLELEMENT(ITEM_CD,','||ITEM_CD ) order by gbn)
       , SUBSTR(XMLAGG( XMLELEMENT(ITEM_CD,','||ITEM_CD ) ORDER BY GBN ).EXTRACT('//text()').GetStringVal(), 2) as a
  FROM   TAB
GROUP BY GBN