db/oracle

oracle xml parsing

strange-dev 2024. 2. 6. 09:00
반응형

xml 관련 내용 작성 내용 이전글 보시면 됩니다.

별로 특별한 건 없습니다.

그냥 mssql먼저 작성하다 보니 xml에 대한 약간의 내용을 작성했습니다.

oracle은 바로 xml 파싱( parsing) 예제를 작성하겠습니다.

2024.02.05 - [db/mssql] - mssql xml parsing

 

mssql xml parsing

요즘은 xml을 사용하지 않습니다.. 하지만 가끔 레가시 시스템 연동 시 xml을 사용할 수 있는경우가 생기기도 합니다. 아니면 그냥 xml을 좋아해서 일지도 모릅니다. 요즘은 json을 사용합니다. 예전

st-d.tistory.com

 

xml 요소 조회 쿼리
  •  EXTRACTVALUE ( VALUE( XMLSEQUENCE값 ), 'XML 파싱경로') 

      파싱 경로는 대소문자를 구분합니다.

WITH LISTS AS (
    SELECT EXTRACTVALUE ( VALUE ( STR ), '/LISTS/A' ) A
        ,EXTRACTVALUE ( VALUE ( STR ), '/LISTS/B' ) B
        ,EXTRACTVALUE ( VALUE ( STR ), '/LISTS/C' ) C
    FROM TABLE 
    ( XMLSEQUENCE 
        ( EXTRACT 
            ( XMLTYPE (
               '<LISTS>
                    <A>A1</A>
                    <B>B1</B>
                    <C>BC</C>
                </LISTS>' )
            , '/LISTS' )
        )
    ) STR
)
SELECT A.A,A.B,A.C
FROM LISTS A

 

 

xml 속성 조회 쿼리
WITH lists AS (
    SELECT EXTRACTVALUE ( VALUE ( STR ), '/lists/@attr1' ) attr1
        ,EXTRACTVALUE ( VALUE ( STR ), '/lists/@attr2' ) attr2
        ,EXTRACTVALUE ( VALUE ( STR ), '/lists/@attr3' ) attr3
    FROM TABLE 
    ( XMLSEQUENCE 
        ( EXTRACT 
            ( xmltype (
               '<lists attr1="11" attr2="22" attr3="33"></lists>' )
            , '/lists' )
        )
    ) STR
)
SELECT 
a.attr1,a.attr2,a.attr3
FROM lists a

 

xml 리스트 조회 쿼리
WITH LISTS AS (
    SELECT EXTRACTVALUE ( STR.COLUMN_VALUE, '/LIST/ID' ) ID
        ,EXTRACTVALUE ( STR.COLUMN_VALUE, '/LIST/NM' ) NM
        ,EXTRACTVALUE ( STR.COLUMN_VALUE, '/LIST/DESC' ) "DESC"
        ,EXTRACTVALUE ( STR.COLUMN_VALUE, '/LIST/@ATTR1' ) ATTR_L1
        ,EXTRACTVALUE ( STR.COLUMN_VALUE, '/LIST/@ATTR2' ) ATTR_L2
    FROM TABLE 
    ( XMLSEQUENCE 
        ( EXTRACT 
            ( XMLTYPE (
               '<LISTS>
                    <LIST ATTR1="ATTRL_11" ATTR2="ATTRL_21">
                        <ID>ID_1</ID>
                        <NM>TEST</NM>
                        <DESC>TEST_DESC</DESC>
                    </LIST>
                    <LIST ATTR1="ATTRL_21" ATTR2="ATTRL_22">
                        <ID>ID_2</ID>
                        <NM>TEST2</NM>
                        <DESC>TEST2_DESC</DESC>
                    </LIST>
                    <LIST ATTR1="ATTRL_31" ATTR2="ATTRL_32">
                        <ID>ID_3</ID>
                        <NM>TEST3</NM>
                        <DESC>TEST3_DESC</DESC>
                    </LIST>
                </LISTS>' )
            , '/LISTS/LIST' )
        )
    ) STR
)
SELECT A.ID
,A.NM
,A."DESC"
,A.ATTR_L1
,A.ATTR_L2
FROM LISTS A

 

xml 요소, 속성, 리스트리스트 한 번에 조회 쿼리
WITH LISTS AS (
    SELECT EXTRACTVALUE ( VALUE ( STR ), '/LISTS/@ATTR1' ) ATTR1
        ,EXTRACTVALUE ( VALUE ( STR ), '/LISTS/@ATTR2' ) ATTR2
        ,EXTRACTVALUE ( VALUE ( STR ), '/LISTS/@ATTR3' ) ATTR3
        ,EXTRACTVALUE ( VALUE ( STR ), '/LISTS/A' ) A
        ,EXTRACTVALUE ( VALUE ( STR ), '/LISTS/B' ) B
        ,EXTRACTVALUE ( VALUE ( STR ), '/LISTS/C' ) C
        ,EXTRACT ( VALUE ( STR ), '/LISTS/LIST' ) XML_DATA
    FROM TABLE 
    ( XMLSEQUENCE 
        ( EXTRACT 
            ( XMLTYPE (
               '<LISTS ATTR1="11" ATTR2="22" ATTR3="33">
                    <A>A1</A>
                    <B>B1</B>
                    <C>BC</C>
                    <D>1</D>
                    <LIST ATTR1="ATTRL_11" ATTR2="ATTRL_21">
                        <ID>ID_1</ID>
                        <NM>TEST</NM>
                        <DESC>TEST_DESC</DESC>
                    </LIST>
                    <LIST ATTR1="ATTRL_21" ATTR2="ATTRL_22">
                        <ID>ID_2</ID>
                        <NM>TEST2</NM>
                        <DESC>TEST2_DESC</DESC>
                    </LIST>
                    <LIST ATTR1="ATTRL_31" ATTR2="ATTRL_32">
                        <ID>ID_3</ID>
                        <NM>TEST3</NM>
                        <DESC>TEST3_DESC</DESC>
                    </LIST>
                </LISTS>' )
            , '/LISTS' )
        )
    ) STR
)
SELECT 
-- 요소,속성
A.ATTR1,A.ATTR2,A.ATTR3
,A.A,A.B,A.C

-- 하위리스트
,EXTRACTVALUE ( LIST.COLUMN_VALUE, '/LIST/ID' ) ID
,EXTRACTVALUE ( LIST.COLUMN_VALUE, '/LIST/NM' ) NM
,EXTRACTVALUE ( LIST.COLUMN_VALUE, '/LIST/DESC' ) "DESC"
,EXTRACTVALUE ( LIST.COLUMN_VALUE, '/LIST/@ATTR1' ) ATTR_L1
,EXTRACTVALUE ( LIST.COLUMN_VALUE, '/LIST/@ATTR2' ) ATTR_L2
FROM LISTS A CROSS JOIN TABLE ( XMLSEQUENCE ( EXTRACT ( A.XML_DATA, '/LIST' ) ) ) LIST;

 

쿼리 결과

요소,속성, 리스트 한번에 조회 결과

 

 

oracle 은 mssql보다 쿼리가 복잡해서 좀 더 어려운 쿼리도 작성해 보았습니다.

반응형