No. 18182
XMLTYPE COLUMN에 대한 DDL, DML, QUERY 예제 (9I)
==========================================
PURPOSE
-------
Oracle8i에서는 XML문서는 데이타베이스에 저장하기 위해서는 xml 문서의 각
attribute를 테이블의 column과 1대1로 대응시켜 저장하거나, 그렇지 않은
경우 XML문서 자체를 한 컬럼에 저장하기 위해서는 CLOB type을 이용하였다.
Oracle9i에서는 XNLType이라는 새로운 type이 제공되어 XML문서를 저장하고
handling하는데 좀 더 유용해졌다.
이 문서에서는 이 XMLType에 대한 기본적인 DDL및, DML, 그리고 조회 방법을
예로 들어 살펴본다.
Explanation
-----------
아래에는 다음 다섯가지에 대해 간단한 예제 문장을 살펴본다.
이때 po_xml_tab라는 이름으로 상품 주문 내역을 포함하는 테이블을 기본 예로
한다.
1. XMLType 컬럼의 생성, 추가, 삭제 방법
2. XMLType column에 XML문서 저장 방법
3. XMLType column에 저장된 XML문서 변경 방법
4. XMLType column 조건을 이용하여 데이타 삭제 방법
5. XMLType column 조회 방법
이 예문에서 사용되는 것으로 미리 확인하여 하는 사항들을 먼저 살펴본다.
(1) sys.XMLType.createXML
sys.XMLType.createXML는 XML문서를 컬럼에 저장할 때 사용되는데, 이
createXML function은 입력되는 XML이 구조화 되었는지를 check해주나,
유효성은 검사하지 못한다.
(2) XMLType 컬럼에 사용하는 extract와 existsnode
extract는 XPath expression을 통해 하나 이상의 node를 추출하게 되는데,
그 node는 elements일수도 있고, attricutes나 text nodes 일 수 있다.
existsnode의 경우는 주어진 XPath evaluation에 XML element나 text node가
만족하면 1을 return하고 그렇지 않으면 0을 return하게 된다.
Example
-------
1. XMLType 컬럼의 생성, 추가, 삭제 방법
(a) 다음과 같이 purchase order document column을 포함하는 purchase order
table을 생성한다.
SQL>CREATE TABLE po_xml_tab
(
poid number,
poDoc SYS.XMLTYPE
);
(b) customer document column을 추가한다.
SQL>ALTER TABLE po_xml_tab ADD (custDoc sys.XMLType);
(c) (b)에서 생성한 column을 drop한다.
SQL>ALTER TABLE po_xml_tab DROP (custDoc sys.XMLType);
2. XMLType column에 XML문서 저장 방법
아래와 같이 XML문서를 insert 시킬 수 있다.
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>'));
3. XMLType column에 저장된 XML문서 변경 방법
purchase order number의 값이 1인 document를 update하는 예이다.
PO element의 child element인 PONO의 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;
4. XMLType column 조건을 이용하여 데이타 삭제 방법
purchase order name이 Po_2인 purchase order 를 delete하는 예이다.
where절은 앞의 3번과 같은 방식이다. 단, getStringVal()은 text value를
string값으로 변환시켜주는 역할을 한다.
SQL>DELETE FROM po_xml_tab e
WHERE e.poDoc.extract('/PO/PNAME/text()').getStringVal()='Po_2';
5. XMLType column 조회 방법
(a) 아래의 예제는 PO의 child element로 PONO가 존재하면서 poid column의 값이
1보다 큰 row를 select하게 된다.
이때 select list에 XMLType column을 select하면서 extract function을
사용하여 purchase order number인 PONO값을 조회하게 된다.
SQL>SELECT e.poDoc.extract('/PO/PONO/text()').getNumberVal() pono
FROM po_xml_tab e
WHERE e.poDoc.existsnode('/PO/PONO') = 1 AND poid > 1;
(b) 유사하지만 (a)보다는 좀 더 복잡한 형태의 query이다.
아래의 예제에서는 shipping address를 포함하고, purchase order number가
2001이고, custoner name으로 John을 포함하는 purchase order name을
조회한다.
/PO/PONO대신 //PONO로 나타낼 수 있고, //CUSTNAME/text()대신 바로
//@CUSTNAME으로 표시할 수 있다.
SQL>SELECT e.poDoc.extract('/PO/PNAME/text()').getStringVal() PNAME
FROM po_xml_tab e
WHERE e.poDoc.existsNode('/PO/SHIPADDR') = 1 AND
e.poDoc.extract('//PONO/text()').getNumberVal() = 2001 AND
e.poDoc.extract('//@CUSTNAME').getStringVal() LIKE '%John%';
Reference Documents
-------------------
<Note:177729.1>
***** 아름다운프로님에 의해서 게시물 복사 + 카테고리변경되었습니다 (2003-12-18 16:49)
