꿈꾸는 개발자, DBA 커뮤니티 구루비

XMLType은 Oracle9i 이후 버전에 저장되는 XML 데이타를 작성, 추출 및 인덱스싱하는 강력한 메커니즘을 제공하는 자체 고유 함수를 가지고 있다.

XMLType 컬럼을 갖는 테이블 생성

  
SQL> CREATE TABLE po_xml_tab(
        poid number,
        poDoc SYS.XMLTYPE
     );

XMLType 컬럼의 생성, 추가, 삭제 방법

  
-- 컬럼의 추가 
SQL> ALTER TABLE po_xml_tab ADD (custDoc sys.XMLType);

-- 테이블 정보 확인
SQL> DESC  po_xml_tab;
 이름            유형
 --------------- --------------
 POID            NUMBER
 PODOC           SYS.XMLTYPE
 CUSTDOC         SYS.XMLTYPE


-- 컬럼의 삭제
SQL> ALTER TABLE po_xml_tab DROP (custDoc ); 

-- 테이블 정보 확인
SQL> DESC  po_xml_tab;
 이름         유형
 ------------ --------------
 POID         NUMBER
 PODOC        SYS.XMLTYPE

XMLType column에 XML문서 저장 방법

  
-- XML문서 저장
SQL> INSERT INTO po_xml_tab VALUES
    (100, sys.XMLType.createXML('
      <PO> <PONO>1</PONO>
         <PNAME>Po_1</PNAME>
         <CUSTNAME>John</CUSTNAME>
         <SHIPADDR>
             <STREET>1033, Main Street</STREET>
             <CITY>Sunnyvalue</CITY>
             <STATE>CA</STATE>
        </SHIPADDR>
     </PO>'));

-- commit
SQL> COMMIT;

-- SQLPlus에서 Display 문자 설정
SQL> SET LONG 10000

-- 저장된 XML문서 조회
SQL> SELECT podoc FROM po_xml_tab;

PODOC
---------------------------------------
<PO>
  <PONO>1</PONO>
  <PNAME>Po_1</PNAME>
  <CUSTNAME>John</CUSTNAME>
  <SHIPADDR>
    <STREET>1033, Main Street</STREET>
    <CITY>Sunnyvalue</CITY>
    <STATE>CA</STATE>
  </SHIPADDR>
</PO>

XMLType column에 저장된 XML문서 변경 방법

sys.XMLType.createXML는 XML문서를 컬럼에 저장할 때 사용되는데, 이 createXML function은 입력되는 XML이 구조화 되었는지를 check해주나, 유효성은 검사하지 못한다.

XPath expression을 통해 하나 이상의 node를 추출하게 되는데, 그 node는 elements 일수도 있고, attricutes나 text nodes 일 수도 있다.

  
-- PONO element의 text값이 1인 것을 찾아서 update하는 예제이다.
-- getNumberVal()은 text value를 numeric값으로 변환시킨다.
SQL> UPDATE po_xml_tab e 
     SET e.poDoc = sys.XMLType.createXML( 
              '<PO pono="2"> 
               <PNAME>Po_2</PNAME> 
               <CUSTNAME>Nance</CUSTNAME> 
               <SHIPADDR> <STREET>2 Avocet Drive</STREET> 
                        <CITY>Redwood Shores</CITY> 
                         <STATE>CA</STATE> 
               </SHIPADDR> 
            </PO>') 
    WHERE e.poDoc.extract('/PO/PONO/text()').getNumberVal() = 1; 

SQL> COMMIT;

-- 수정 결과 확인
SQL> SELECT podoc FROM po_xml_tab;
PODOC
------------------------------------------
 <PO pono="2">
  <PNAME>Po_2</PNAME>
  <CUSTNAME>Nance</CUSTNAME>
  <SHIPADDR>
    <STREET>2 Avocet Drive</STREET>
    <CITY>Redwood Shores</CITY>
    <STATE>CA</STATE>
  </SHIPADDR>
</PO>

XMLType column 조회 방법

  
-- /PO/SHIPADDR/STATE element의 TEXT값이 'CA'인 XML의 PNAME을 조회하는 예제이다.
SQL> SELECT e.poDoc.extract('/PO/PNAME/text()').getStringVal() pname 
     FROM po_xml_tab e 
     WHERE e.poDoc.extract('/PO/SHIPADDR/STATE/text()').getStringVal() = 'CA'

PNAME
----------
Po_2

XMLType column 조건을 이용하여 데이타 삭제 방법

  
-- purchase order name이 Po_2인 purchase order 를 delete하는 예 이다.
-- getStringVal()은 text value를 string값으로 변환시켜주는 역할을 한다.
SQL> DELETE FROM po_xml_tab e 
     WHERE e.poDoc.extract('/PO/PNAME/text()').getStringVal()='Po_2';