[펌]XMLTYPE 기본 사용 예

이 강좌는 Oracle Technical Bulletins No. 18182 XMLTYPE COLUMN에 대한 DDL, DML, QUERY 예제(9I)를 정리한 내용 입니다.

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

아래는 XMLType의 사용 예제 입니다 

1. XMLType 컬럼을 갖는 테이블 생성
 
 SQL> CREATE TABLE po_xml_tab(
        poid number,
        poDoc SYS.XMLTYPE
       );

테이블이 생성되었습니다.

 

2. 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

 

3. 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>'));
1 개의 행이 만들어졌습니다.

-- 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>

 

4. XMLType column에 저장된 XML문서 변경 방법
 
 
-- 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;
1 행이 갱신되었습니다.

SQL> COMMIT;
커밋이 완료되었습니다.

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

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

-- 수정 결과 확인
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>

내용이 변경 된 것을 확인 할 수 있습니다.

 

5. 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

 

6. 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';

1 행이 삭제되었습니다.