Oracle Database TIP
XMLTYPE 기본 사용 예제 15 5 15,851

by 김정식 XMLTYPE CREATE XML EXTRACT GETSTRINGVAL() GETNUMBERVAL() [2005.10.31]


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

- 강좌 URL : http://www.gurubee.net/lecture/1871

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 김정식 [2005.12.01 21:09:06]
EXISTSNODE(warehouse_spec, '/Warehouse/Docks') = 1


by 김정식 [2005.12.02 11:20:00]
Attribute를 찾아갈때는 @연산자를 이용

SELECT podoc
FROM po_xml_tab
WHERE extractValue(podoc, '/PO/@pono') = '2'

pono가 2인 데이타를 조회

by 김정식 [2005.12.02 11:25:17]
- UPDATEXML 함수 : elements의 text값을 수정 할 수 있음.

-- CUSTNAME을 John으로 수정한 예제.
UPDATE po_xml_tab
SET poDoc = UPDATEXML(poDoc,
'/PO/CUSTNAME/text()', 'John');

-수정 결과

John


by 김정식 [2005.12.02 11:30:27]
여러개의 동일한 element가 존재할 경우
배열을 이용해서 ex([index]) 원하는 n번째
elements의 값을 찾아 올 수 있음.

-- 첫번째 pname의 값
e.poDoc.extract(//PO/PNAME[1]/text()').getStringVal()

-- 두분째 pname의 값
e.poDoc.extract(//PO/PNAME[2]/text()').getStringVal()
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입