Oracle de XMLDB #18 - Relational dataをXMLへ #1 Tweet
Oracle XML DBリポジトリ関連の話はしばらく脇に置いておき、これから数回に分けてリレーショナル表からXMLを生成する方法について、備忘録の意味も含めて書いておくことにする。
● 環境データ
Server : Dynabook SS SX/210LNLW (RAM:768MB)
CentOS5
Oracle11g EE R1 11.1.0.6.0 for Linux x86
Client : Apple PowerMac G5 Dual 2.7Ghz (RAM:1GB)
MacOSX Tiger 10.4.11 Server
Oracle10g Instant Client 10g R1 for MacOSX(PPC)
表を問い合わせXMLTypeインスタンスを返す方法は複数ある。まず最初に紹介するのは、XMLTABLE()関数のXqueryでora:view()関数を使う方法。
XMLTABLE()関数は、SQL/XMLで提供される標準関数で、本来はXMLデータをリレーショナルフォーマットへマッピングする為に利用する関数なのだが、今回は本来の目的とは異なる使い方をしてみた。
XMLTABLE()関数でリレーショナル表からXMLTypeインスタンスを取得するには、OracleがXXQuery向に提供している ora:view()関数を利用する。（Oracleが独自に拡張したXQuery向け関数の名前空間は、ora:）
ora:view()関数は、XQueryの標準関数であるfn:doc()関数を利用する感覚で利用できる。
以下の例では、XMLTABLE()関数でCOLUMNS句を指定していしないのがポイント。尚、COLUMN_VALUE疑似列を利用しています。
Last login: Sat Feb 9 06:46:43 on console
Welcome to Darwin!
G5Server:˜ discus$ sqlplus /nolog
SQL*Plus: Release 10.1.0.3.0 - Production on 土 2月 9 15:30:44 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
SCOTT> conn scott/tiger@lampeye
接続されました。
SCOTT> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SCOTT> set pagesize 1000
SCOTT> set long 4000
SCOTT> set sqlterm off
SCOTT> set linesize 80
SCOTT> l
1 select xmldoc.COLUMN_VALUE
2 from
3 xmltable(
4 '<ROWSET>{
5 for $d in ora:view("EMP")
6 return $d
7 }</ROWSET>'
8* ) xmldoc
SCOTT> /
COLUMN_VALUE
--------------------------------------------------------------------------------
<ROWSET><ROW><EMPNO>9999</EMPNO><ENAME>ほげ</ENAME><JOB>ENGINEER</JOB><MGR>7566<
/MGR><HIREDATE>2008-01-01</HIREDATE><SAL>2500</SAL><DEPTNO>20</DEPTNO></ROW><ROW
><EMPNO>7369</EMPNO><ENAME>SMITH</ENAME><JOB>CLERK</JOB><MGR>7902</MGR><HIREDATE
>1980-12-17</HIREDATE><SAL>800</SAL><DEPTNO>20</DEPTNO></ROW><ROW><EMPNO>7499</E
MPNO><ENAME>ALLEN</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>1981-02-20<
/HIREDATE><SAL>1600</SAL><COMM>300</COMM><DEPTNO>30</DEPTNO></ROW><ROW><EMPNO>75
21</EMPNO><ENAME>WARD</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>1981-02
-22</HIREDATE><SAL>1250</SAL><COMM>500</COMM><DEPTNO>30</DEPTNO></ROW><ROW><EMPN
O>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>198
1-04-02</HIREDATE><SAL>2975</SAL><DEPTNO>20</DEPTNO></ROW><ROW><EMPNO>7654</EMPN
O><ENAME>MARTIN</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>1981-09-28</H
IREDATE><SAL>1250</SAL><COMM>1400</COMM><DEPTNO>30</DEPTNO></ROW><ROW><EMPNO>769
8</EMPNO><ENAME>BLAKE</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>1981-05-
01</HIREDATE><SAL>2850</SAL><DEPTNO>30</DEPTNO></ROW><ROW><EMPNO>7782</EMPNO><EN
AME>CLARK</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>1981-06-09</HIREDATE
><SAL>2450</SAL><DEPTNO>10</DEPTNO></ROW><ROW><EMPNO>7839</EMPNO><ENAME>KING</EN
AME><JOB>PRESIDENT</JOB><HIREDATE>1981-11-17</HIREDATE><SAL>5000</SAL><DEPTNO>10
</DEPTNO></ROW><ROW><EMPNO>7844</EMPNO><ENAME>TURNER</ENAME><JOB>SALESMAN</JOB><
MGR>7698</MGR><HIREDATE>1981-09-08</HIREDATE><SAL>1500</SAL><COMM>0</COMM><DEPTN
O>30</DEPTNO></ROW><ROW><EMPNO>7900</EMPNO><ENAME>JAMES</ENAME><JOB>CLERK</JOB><
MGR>7698</MGR><HIREDATE>1981-12-03</HIREDATE><SAL>950</SAL><DEPTNO>30</DEPTNO></
ROW><ROW><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR>
<HIREDATE>1981-12-03</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO></ROW><ROW><EMP
NO>7934</EMPNO><ENAME>MILLER</ENAME><JOB>CLERK</JOB><MGR>7782</MGR><HIREDATE>198
2-01-23</HIREDATE><SAL>1300</SAL><DEPTNO>10</DEPTNO></ROW></ROWSET>
経過: 00:00:00.11
SCOTT>
これだとXML宣言が無い。ではどうやってXML宣言を付加するかと言えば、XMLRoot()関数を使う。
XMLRoot()関数は2005年まではSQL/XMLの標準関数だったが現在は標準関数ではない。但し、Oracleでは利用できるのでXMLRoot()関数を利用してXML宣言を付加することができる。
では早速試してみよう！。
SCOTT> select
2 xmlroot(xmldoc.column_value,version '1.0')
3 from
4 xmltable(
5 '<ROWSET>{
6 for $d in ora:view("EMP")
7 return $d
8 }</ROWSET>'
9 ) xmldoc
10 /
XMLROOT(XMLDOC.COLUMN_VALUE,VERSION'1.0')
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPNO>9999</EMPNO>
<ENAME>ほげ</ENAME>
<JOB>ENGINEER</JOB>
<MGR>7566</MGR>
<HIREDATE>2008-01-01</HIREDATE>
<SAL>2500</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>1980-12-17</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>1981-02-20</HIREDATE>
<SAL>1600</SAL>
<COMM>300</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW>
<EMPNO>7521</EMPNO>
<ENAME>WARD</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>1981-02-22</HIREDATE>
<SAL>1250</SAL>
<COMM>500</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW>
<EMPNO>7566</EMPNO>
<ENAME>JONES</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>1981-04-02</HIREDATE>
<SAL>2975</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>
<EMPNO>7654</EMPNO>
<ENAME>MARTIN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>1981-09-28</HIREDATE>
<SAL>1250</SAL>
<COMM>1400</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW>
<EMPNO>7698</EMPNO>
<ENAME>BLAKE</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>1981-05-01</HIREDATE>
<SAL>2850</SAL>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>1981-06-09</HIREDATE>
<SAL>2450</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>1981-11-17</HIREDATE>
<SAL>5000</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>7844</EMPNO>
<ENAME>TURNER</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>1981-09-08</HIREDATE>
<SAL>1500</SAL>
<COMM>0</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW>
<EMPNO>7900</EMPNO>
<ENAME>JAMES</ENAME>
<JOB>CLERK</JOB>
<MGR>7698</MGR>
<HIREDATE>1981-12-03</HIREDATE>
<SAL>950</SAL>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW>
<EMPNO>7902</EMPNO>
<ENAME>FORD</ENAME>
<JOB>ANALYST</JOB>
<MGR>7566</MGR>
<HIREDATE>1981-12-03</HIREDATE>
<SAL>3000</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
<JOB>CLERK</JOB>
<MGR>7782</MGR>
<HIREDATE>1982-01-23</HIREDATE>
<SAL>1300</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
</ROWSET>
経過: 00:00:00.33
SCOTT>
あれ、XMLRoot()関数に渡しているXMLTABLE()関数で取得したXMLTypeインスタンスには改行がありませんでしたが、XMLROOT()関数を利用すると改行が付加されたXMLTypeインスタンスが返されます。また、その影響だと思うが経過時間も増えている。
こりゃ参った〜という感じです。以前もOracle XML DBに関するエントリでwhitespaceのpreserve/stripは現時点のOracle XML DBでは明示的に制御できないということは書いていましたが思わぬところでwhitespaceかってに付加されるという動きには戸惑いますね。（利用する側で制御できるようにしてもらったほうがスッキリするように感じます。）
トレースして詳細に追跡したわけでは無いのであくまで想像の域を脱していないのですが、もしかすると、XMLROOT()関数はXMLTypeインスタンスを受け取りXML宣言を付加して返してくる際、XML宣言を追加したDOMを生成した上でXMLTypeインスタンスを返してくるのではないかと考えています。（くどいようですが、これは私の推測に過ぎませんので誤解なさらないようにしてください。）
時間があればもっと深〜〜〜く追っかけてみたい現象なのでとりあえず、TODOリストに追加しておきますかね。
では次回へつづく。
| 固定リンク | 0
トラックバック
この記事へのトラックバック一覧です： Oracle de XMLDB #18 - Relational dataをXMLへ #1:
» Oracle de XMLDB #18 トラックバック discus
Bookmarked your post over at Blog Bookmarker.com! [続きを読む]
受信： 2008年2月12日 (火) 00時56分
コメント