[Oracle] XMLELEMENT, XMLAGG 함수

여러 줄의 행(ROW)을 한 줄로 이어서 출력하고 싶은 경우가 있다.
이 경우에는, XMLELEMENT 함수와 XMLAGG 함수를 조합하면 된다.

※ Oracle 10g 버전 이상부터는 XMLAGG, WM_CONCAT 함수를 조합하면 보다 간단하게 사용 가능하다.

 

1) XMLELEMENT 함수

: 반환되는 레코드를 XML 요소로 바꾸어주는 함수

XMLELEMENT([태그명], 레코드명)

ex) SHOP_TABLE 테이블의 SHOP_CD 컬럼을 XML <A> 태그로 출력
SELECT XMLEMENET(A, SHOP_CD)
FROM SHOP_TABLE;

첫 번째 인수에는 XML의 요소로 사용할 태그를 지정하고, 그 뒤에는 SELECT할 필드명을 적어준다.
태그명은 옵션 항목으로, 별도로 지정하지 않으면 레코드명 자체가 XML 요소로 반환된다.
따라서, FROM 절 뒤에 나오는 테이블의 레코드는 철저하게 무시된다.

 

 

2) XMLAGG 함수

: XML 요소들로 이루어진 여러 개의 레코드들을 한 줄로 이어서 표현해주는 함수

• XML + AGG(Aggregate) 이고, XML 요소를 집합화한다는 의미
• XML 형식의 레코드를 하나의 ROW로 SELECT 해오기 때문에, XMLELEMENT와 함께 사용할 수 있음

XMLAGG(XML 요소 레코드)

ex) SHOP_TABLE 테이블의 SHOP_CD 컬럼을 XML <A> 태그로 감싸, 1개의 ROW로 출력
SELECT  XMLAGG(XMLEMENET(A, SHOP_CD))
FROM SHOP_TABLE;

 

3) 활용 예제

WITH DATA_TABLE AS (
    SELECT 'A' TITLE, '1' TEXT FROM DUAL
    UNION ALL
    SELECT 'B' TITLE, '2' TEXT FROM DUAL
    UNION ALL
    SELECT 'C' TITLE, '3' TEXT FROM DUAL
    UNION ALL
    SELECT 'D' TITLE, '4' TEXT FROM DUAL
    UNION ALL
    SELECT 'E' TITLE, '5' TEXT FROM DUAL
    UNION ALL
    SELECT 'F' TITLE, '6' TEXT FROM DUAL
    UNION ALL
    SELECT 'G' TITLE, '7' TEXT FROM DUAL
    UNION ALL
    SELECT 'H' TITLE, '8' TEXT FROM DUAL
)
SELECT XMLAGG(XMLELEMENT(A, TITLE || ':' || TEXT)) XML
FROM DATA_TABLE;

 

 

[결과]

<A>A:1</A><A>B:2</A><A>C:3</A><A>D:4</A><A>E:5</A><A>F:6</A><A>G:7</A><A>H:8</A>

 

 

 

[reference]
https://tragramming.tistory.com/76