DATOR


한 컬럼내의 구분자로 연결된 문자열을 종으로(로우로) 분리하는 쿼리_XML타입 활용(3/3) Oracle


이번글에선 XMLTYPE을 활용하여 문자열을 종데이터로 분리하는 쿼리를 설명하겠습니다.


샘플 데이터는 지난글과 동일하고, 아래 쿼리의 인라인뷰(T) 절에 표현했습니다.


XMLTYPE을 활용한 쿼리의 핵심은 구분자를 기준으로 문자열을 XML 문법에 맞도록 변환하는 것입니다.

아래 쿼리의 XMLTYPE('<A><E>' || REPLACE(T.NAME, '|', '</E><E>') || '</E></A>') 함수를 보시면

기존 문자열을 XML 형태로 변환하는 로직인데요, 결과셋는 아래와 같습니다.


<A><E>A1</E><E>B1</E></A>

<A><E>A2</E><E>B2</E><E>C2</E></A>

<A><E>A3</E><E>B3</E><E>C3</E><E>D3</E></A>


XMLTYPE() 함수는 위와 같은 형식의 문자열 데이터를 XMLTYPE 타입으로 형변환을 해줍니다.



TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('<A><E>' || REPLACE(T.NAME, '|', '</E><E>') || '</E></A>'), '/A/E') ) ) IC

이 로직은 XML 데이터에서 '/A/E'(<A><E>)로 시작하는 태그를 추출하여 테이블 형태로 데이터를 변환하는 로직입니다.


마직막으로, SELECT 절의 EXTRACTVALUE(VALUE(IC), '/E') WORD 은 IC 테이블의 값 중에서 '/E'(<E>) 태그에 있는 값들만

추출하여 출력합니다.



SELECT T.*, EXTRACTVALUE(VALUE(IC), '/E') WORD

FROM (

      SELECT 1 NUM, 'A1|B1' NAME  FROM DUAL UNION ALL

      SELECT 2,     'A2|B2|C2'    FROM DUAL UNION ALL

      SELECT 3,     'A3|B3|C3|D3' FROM DUAL

     ) T

    , TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('<A><E>' || REPLACE(T.NAME, '|', '</E><E>') || '</E></A>'), '/A/E') ) ) IC


쿼리 결과 =>
NUM   NAME      WORD
1 A1|B1           A1
1 A1|B1           B1
2 A2|B2|C2       A2
2 A2|B2|C2       B2
2 A2|B2|C2       C2
3 A3|B3|C3|D3   A3
3 A3|B3|C3|D3   B3
3 A3|B3|C3|D3   C3
3 A3|B3|C3|D3   D3

이 쿼리의 장점은 지난 글에서 소개한 쿼리와는 다르게 데이터에 대한 뻥튀기가 발생하지 않습니다.
이전에 소개한 쿼리는 구분자의 개수만큼 문자열 중복이 발생해야만 하는 로직이지만, 적어도 이 쿼리의
로직상에선 의도적인 데이터 뻥튀기가 발생하지 않죠.. 물론 어떤 쿼리가 성능에 더 좋을진 저도 잘 모르겠습니다.

그리고, 이 쿼리는 오라클 버전에 영향을 받을 수 있으니, 사용전에  오라클 버전을 확인해야 합니다.

TAG

Leave Comments