[Oracle] XMLELEMENT, XMLAGG 함수 사용하는 방법

오라클 데이터베이스에서 XMLELEMENT, XMLAGG 함수를 사용하는 방법에 대해 알아보도록 합시다. 해당 함수는 보통 로우를 하나의 로우로 합치는 데 사용을 많이 하는데요, 다음과 같이 조회해 볼 수 있답니다.

  SELECT JOB,
         XMLELEMENT (BS) AS C,
         XMLELEMENT (BS, JOB) AS C,
         XMLELEMENT (BS, JOB, ',') AS C,
         XMLELEMENT (BS, ',', JOB) AS C,
         XMLAGG (XMLELEMENT (BS,
                             JOB,
                             ',',
                             EMPNO))
            AS C,
         XMLAGG (XMLELEMENT (BS, ',', EMPNO) ORDER BY EMPNO) AS C,
         XMLAGG (XMLELEMENT (BS, ',', EMPNO) ORDER BY EMPNO).EXTRACT (
            '//text()')
            AS C,
         SUBSTR (
            XMLAGG (XMLELEMENT (BS, ',', EMPNO) ORDER BY EMPNO).EXTRACT (
               '//text()'),
            2)
            AS C
    FROM EMP
GROUP BY JOB;

XMLELEMENT (BS)

괄호 안에 명시한 태그로 컬럼을 조회합니다. 예제자료는 <BS></BS> 같은 식으로 보입니다. 원하는 태그를 입력하면 됩니다.

XMLELEMENT (BS, JOB)

첫 번째 매개변수의 태그로 job 컬럼을 이너텍스트로 하여 조회합니다. 예제자료는 <BS>ANALYST</BS>와 같이 조회합니다.

XMLELEMENT (BS, JOB, ',')

첫 번째 매개변수는 태그, 두 번째부터는 연결하는 모든 컬럼입니다. 콤마를 하나 더 추가하였기에 <BS>ANALYST,</BS> 와 같이 조회합니다.

XMLELEMENT (BS, ',', JOB)

순서를 변경하면 , job 과 같이 조회할 거라고 예상이 가시나요? 조회 결과 값은 <BS>,ANALYST</BS> 입니다.

XMLAGG (XMLELEMENT (BS, JOB, ',', EMPNO))

XMLAGG 함수로 집계를 해보겠습니다. 해당 예제 자료는 JOB으로 그룹을 하였기에 EMPNO와 같이 그룹의 대상이 되지 않는 컬럼은 XMLAGG를 통해 조회할 수 있습니다. 해당 조회 식의 결과는 JOB, EMPNO를 BS 태그로 감싼 형태입니다. <BS>ANALYST,7788</BS><BS>ANALYST,7902</BS>

EMPNO가 2개 이기에 태그가 2개 생겼으며, 그룹의 개수만큼 태그가 이어졌을 것입니다.