oracle XML 저장(extractValue,updateXML,existsNode)

 

XPath를 이용한 XMLType 데이터 인출 및 검색
XMLType 테이블 혹은 컬럼에 대해 XML 문서 전체나 일부 정보를 얻어오기 위해 W3C 표준인 XPath 표현을 사용할 수 있다. extractValue() 함수와 extract() 함수를 이용해 XMLType에 대해 XPath 연산을 취해 원하는 값을 구하면 된다. <그림 3>은 extractValue() 문법을 나타낸다. extractValue()는 XPath를 만족하는 단일 노드 혹은 하나의 애트리뷰트를 인출할 때 사용되며 검색 조건을 만족하는 노드 혹은 애트리뷰트의 값을 리턴한다.

<그림 3> extractValue() 문법

<리스트 1>은 앞에서 입력한 BookStore XMLType 테이블에 대해 다양한 XPath 조건을 입력해 조건을 만족하는 XML 노드의 값을 검색하는 SQL문과 그 결과 값을 나타낸다.
value() 함수는 XMLType 테이블을 인자로 받아 해당 테이블의 XMLType 값을 리턴한다. XMLType 컬럼의 경우는 value()를 사용하지 않고 컬럼 이름을 바로 사용한다.

<리스트 1> extractValue()를 이용한 단일 노드 혹은 애트리뷰트 인출
SQL>rem BookStore의 자식 노드 중 첫 번째 Book의 Title 값 인출
SQL>SELECT extractValue (value(x),
‘/BookStore/Book[position()=1]/Title')
FROM BookStore x;

EXTRACTVALUE(VALUE(X),'/BOOKSTORE/BOOK[POSITION()=1]/TITLE')
------------------------------------------------------------
My Life and Times

SQL>rem BookStore의 자식 노드 중 ISBN이 다음과 같은 Book의 Title 값 인출
SQL>SELECT extractValue(value(x),
‘/BookStore/Book[ISBN=“0-440-34319-4”]/Title’)
FROM BookStore x;

EXTRACTVALUE(VALUE(X),'/BOOKSTORE/BOOK[ISBN="0-440-34319-4"]/TITLE')
Illusions The Adventures of a Reluctant Messiah

SQL>rem BookStore의 자식 노드 중 Title이 다음과 같은 Book의 Author 값 인출
SQL>SELECT extractValue(value(x),
‘/BookStore/Book[Title=“The First and Last Freedom”]/Author’)
FROM BookStore x;

EXTRACTVALUE(VALUE(X),'/BOOKSTORE/BOOK[TITLE="THEFIRSTANDLASTFREEDOM"]/AUTHOR')
------------------------------------------------------------
J. Krishnamurti

extractValue() 함수는 XPath의 결과 값을 만족하는 하나의 노드나 애트리뷰트 값을 인출하는 함수이므로 노드나 애트리뷰트가 XPath의 결과로 여러 개 반환되는 경우에는 다음과 같이 에러를 반환한다. <리스트 2>는 extractValue()를 잘못 이용한 경우와 결과를 나타낸다.

<리스트 2> extractValue()를 잘못 이용한 경우와 결과
SQL>rem BookStore는 여러 개의 Book 노드를 가지고 있으므로 조건을 만족하는 Title 노드도 여러 개다.
SQL>SELECT extractValue(value(x),
‘/BookStore/Book/Title’)
FROM BookStore x;

FROM BookStore x
*
ERROR at line 3:
ORA-19025: EXTRACTVALUE returns value of only one node

SQL>rem BookStore는 여러 개의 Book 노드를 가지고 있다.
SQL>SELECT extractValue(value(x),
‘/BookStore/Book’)
FROM BookStore x;

FROM BookStore x
*
ERROR at line 3:
ORA-19025: EXTRACTVALUE returns value of only one node

SQL>rem BookStore XML 내에는 여러 개의 ISBN 노드가 존재한다.
SQL>SELECT extractValue(value(x),
‘//ISBN’)
FROM BookStore x;

FROM BookStore x
*
ERROR at line 3:
ORA-19025: EXTRACTVALUE returns value of only one node


extract()는 XPath 표현을 만족하는 노드가 여러 개인 경우에 사용하며, 그 결과로 검색 결과의 노드들을 모두 모아 하나의 XMLType으로 반환한다. <그림 4>는 extract()를 사용하는 문법을 나타낸다.

<그림 4> extract() 문법

<리스트 3>은 extract()를 이용해 다중 노드를 XMLType으로 인출하는 예를 나타낸다. BookStore XMLType 테이블에 저장된 레코드 수가 1개이므로 모든 extract()를 이용한 SQL문의 결과 레코드 수는 1개이며 그 레코드들은 여러 개의 노드를 가지는 XMLType이다.

<b><리스트 3> extract()를 이용한 다중 노드를 XMLType으로 인출M</b>
SQL>set long 4000
SQL>rem BookStore의 자식인 Book의 Title 노드를 모아서 인출
SQL>SELECT extract (value(x),
‘/BookStore/Book/Title’)
FROM BookStore x;

EXTRACT (VALUE(X),'/BOOKSTORE/BOOK/TITLE')
-----------------------------------------
<Title>My Life and Times</Title>
<Title>Illusions The Adventures of a Reluctant Messiah</Title>
<Title>The First and Last Freedom</Title>

SQL>rem BookStore의 자식인 Book을 모아서 인출
SQL>SELECT extract(value(x),
‘/BookStore/Book’)
FROM BookStore x;

EXTRACT(VALUE(X),'/BOOKSTORE/BOOK')
-----------------------------------
<Book>
<Title>My Life and Times</Title>
<Author>Paul McCartney</Author>
<Date>1998</Date>
< ISBN>1-56592-235-2</ISBN>
<Publisher>McMillin Publishing</Publisher>
</Book>
<Book>
<Title>Illusions The Adventures of a Reluctant Messiah</Title>
<Author>Richard Bach</Author>
<Date>1977</Date>
<ISBN>0-440-34319-4</ISBN>
<Publisher>Dell Publishing Co.</Publisher>
</Book>
<Book>
<Title>The First and Last Freedom</Title>
<Author>J. Krishnamurti</Author>
<Date>1954</Date>
<ISBN>0-06-064831-7</ISBN>
<Publisher>Harper Row</Publisher>
</Book>

SQL>rem BookStore의 모든 ISBN 노드를 모아서 인출
SQL>SELECT extract (value(x),
'//ISBN')
FROM BookStore x;

EXTRACT (VALUE(X), '//ISBN')
------------------------
<ISBN>1-56592-235-2</ISBN>
<ISBN>0-440-34319-4</ISBN>
<ISBN>0-06-064831-7</ISBN>


updateXML()을 이용한 XML 문서 변경
existsNode() 함수는 XMLType 타입에 대해 XPath를 만족하는 노드가 있는 경우에 true(1)를, 그렇지 않은 경우 false(0)를 리턴하는 함수이다. <그림 5>는 existsNode()의 문법을 나타낸다.

<그림 5> existsNode() 문법

<리스트 4>는 existsNode()를 이용해 BookStore XMLType 테이블에 대해 XPath 조건 검색을 하는 SQL문과 그 결과를 나타낸다.

<리스트 4> existsNode()를 이용한 XML 문서 내용 조건 검색
SQL>rem BookStore의 자식 노드 중 Title 노드를 가진 Book 노드를 검색
SQL>SELECT existsNode (value(x),
‘/BookStore/Book/Title’)
FROM BookStore x;

EXISTSNODE (VALUE(X), ‘/BOOKSTORE/BOOK/TITLE’)
--------------------------------------------
1
SQL>rem BookStore의 자식 노드 중 Title이 “The First and Last Freedom”인 Book 노드를 가진 레코드 수 검색
SQL>SELECT count (*)
FROM BookStore x
WHERE existsNode (value(x),
‘//Book[Title=’The First and Last Freedom“]‘)=1;

COUNT(*)
----------
1

SQL>rem BookStore의 자식 노드 중 ISBN이 “0000”인 Book의 Title 노드 검색
SQL>SELECT existsNode (value(x),
‘//Book[ISBN=“0000”]/Title’)
FROM BookStore x;

EXISTSNODE (VALUE(X),'//BOOK[ISBN="0000"]/TITLE')
------------------------------------------------
0
SQL>rem BookStore의 자식 노드 중 ISBN이 “0000”인 Book의 Date 노드를 가진 레코드 삭제
SQL>DELETE FROM BookStore x
WHERE existsNode (value(x), ‘//Book[ISBN=“0000”]/Date’)=1;

0 rows deleted.


XMLType을 변경하기 위해서는 updateXML() 함수를 이용한다. updateXML()의 문법은 <그림 6>과 같다. <리스트 5>는 updateXML()을 이용해 BookStore XMLType 테이블의 내용을 수정하는 예를 나타낸다.

<그림 6> updateXML() 문법

.

<리스트 5> updateXML()을 이용한 XML 문서 내용 수정
SQL>set long 4000
SQL>rem BookStore의 차일드 중 Title이 “My Life and Times”인 Book 노드의 Date 값을 “2003”으로 수정하는 예제
SQL>UPDATE BookStore book
SET value (book) =
UpdateXML (value (book),
‘/BookStore/Book/Date/text()’, ‘2003’)
WHERE existsNode (value (book),
‘/BookStore/Book[Title=“My Life and Times”]“)=1;

1 row updated.

SQL>rem updateXML()로 수정한 결과를 확인하는 SQL문
SQL>SELECT extract (value (book),
'BookStore/Book[Title=“My Life and Times”]“)
FROM BookStore book;

EXTRACT (VALUE (BOOK),'BOOKSTORE/BOOK[TITLE="MYLIFEANDTIMES"]')
------------------------------------------------------------
<Book>
<Title>My Life and Times</Title>
<Author>Paul McCartney</Author>
<Date>2003</Date>
<ISBN>1-56592-235-2</ISBN>
<Publisher>McMillin Publishing</Publisher>
</Book>

[출처] 오라클 9i에 XML 저장하기 - 2 |작성자 aragagi

 

예제 :

WITH TEST AS (SELECT '<Data>
<Good>
<CallNumber>TES</CallNumber>
<TwitterUrl>TEST</TwitterUrl>
<FacebookUrl>TEST</FacebookUrl>
<EnableRemoteSvc>Y</EnableRemoteSvc>
</Good>
</Data>' ADD_INF6 FROM DUAL)
SELECT EXTRACTVALUE(XMLTYPE(NVL(ADD_INF6, '<Data/>')), 'Data/Good/CallNumber') AS CALLNO
FROM TEST

------------------------

결과 : TES