« 日本の借金時計 - java applet版 久々にバージョンアップしました | トップページ | Oracle de XMLDB #23 - Relational dataをXMLへ #6 »

2008年2月19日 (火)

Oracle de XMLDB #22 - Relational dataをXMLへ #5

リレーショナルデータを取り出してからXMLへ変換する方法へ行く前にもう一つあるのを忘れてました。
SYS_XMLAGG()XMLELEMENT()XMLFOREST()関数を利用する方法。
Oracle9i R1から提供されていた関数だったが、古くから提供されていたので完璧に忘れてました。m(_ _)m
(マニュアルを調べた上でOracle9i R1から提供されていた関数と書いたが・・・もし、Oracle8i 8.1.7あたりからあったよ!というツッコミがあればご遠慮なく。)


バックナンバー:
Oracle de XMLDB #18 - Relational dataをXMLへ #1

Oracle de XMLDB #19 - Relational dataをXMLへ #2
Oracle de XMLDB #20 - Relational dataをXMLへ #3
Oracle de XMLDB #21 - Relational dataをXMLへ #4


2008/2/19 9:0:0
文字参照のミスを修正しました。


尚、他のXQueryの結果に関してはいままでもこちらでは制御できない改行が挿入された結果だったり、逆に取り除かれた結果が返されたりしていたが、前述の関数を利用した場合は全体に整形用改行が入るのではなく、SYS_XMLAGG()関数でマージされたXMLELEMENT()の結果からは改行は取り除かれているが、XML宣言やルート要素タグの後には何故か改行が付加される。(要素間の改行やインデントなどは目視する際に見やすくするためなので、あっても無くていいのだが、なんとも気持ちの悪い付け方をしてくれる。。。)

また、いままで実行してきた各SQL文は、それぞれ2回実行してからalter system flush shared_poolを1度実行した後に実行してある。(実行環境は以前と同じ

SQL*Plus: Release 10.1.0.3.0 - Production on 火 2月 19 00:00:09 2008

Copyright (c) 1982, 2004, Oracle. All rights reserved.

> conn scott/tiger@lampeye
接続されました。
SCOTT> set long 4000
SCOTT> set pagesize 1000
SCOTT> select
2 sys_xmlagg(
3 xmlelement("ROW",
4 xmlforest(
5 empno,
6 ename,
7 job,
8 mgr,
9 hiredate,
10 sal,
11 comm,
12 deptno
13 ))) xmldoc
14 from
15 emp
16 order by
17 empno;

XMLDOC
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW><EMPNO>9999</EMPNO><ENAME>ほげ</ENAME><JOB>ENGINEER</JOB><MGR>7566</MGR><HI
REDATE>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><EN
AME>ALLEN</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>1981-02-20</HIREDAT
E><SAL>1600</SAL><COMM>300</COMM><DEPTNO>30</DEPTNO></ROW><ROW><EMPNO>7521</EMPN
O><ENAME>WARD</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>1981-02-22</HIR
EDATE><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</HIRE
DATE><SAL>2850</SAL><DEPTNO>30</DEPTNO></ROW><ROW><EMPNO>7782</EMPNO><ENAME>CLAR
K</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>1981-06-09</HIREDATE><SAL>24
50</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</DE
PTNO></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><HIREDAT
E>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.41
SCOTT>


ちなみに、もうお気づきだと思うが、上記のようにorder by句を付けたとしてもエラーにはならないが指定した列でソートされることはないのでご注意を!
ソートした結果が欲しい場合には副問い合せを利用するとよいだろう。


SCOTT> select
2 sys_xmlagg(
3 xmlelement("ROW",
4 xmlforest(
5 empno,
6 ename,
7 job,
8 mgr,
9 hiredate,
10 sal,
11 comm,
12 deptno
13 ))) xmldoc
14 from
15 (select * from emp order by empno) alias_of_emp
16 /

XMLDOC
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW><EMPNO>7369</EMPNO><ENAME>SMITH</ENAME><JOB>CLERK</JOB><MGR>7902</MGR><HIRE
DATE>1980-12-17</HIREDATE><SAL>800</SAL><DEPTNO>20</DEPTNO></ROW><ROW><EMPNO>749
9</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><EMPN
O>7521</EMPNO><ENAME>WARD</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>198
1-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-2
8</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</HIRE
DATE><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><DEPTN
O>10</DEPTNO></ROW><ROW><EMPNO>7844</EMPNO><ENAME>TURNER</ENAME><JOB>SALESMAN</J
OB><MGR>7698</MGR><HIREDATE>1981-09-08</HIREDATE><SAL>1500</SAL><COMM>0</COMM><D
EPTNO>30</DEPTNO></ROW><ROW><EMPNO>7900</EMPNO><ENAME>JAMES</ENAME><JOB>CLERK</J
OB><MGR>7698</MGR><HIREDATE>1981-12-03</HIREDATE><SAL>950</SAL><DEPTNO>30</DEPTN
O></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><ROW><EMPNO>9999</
EMPNO><ENAME>ほげ</ENAME><JOB>ENGINEER</JOB><MGR>7566</MGR><HIREDATE>2008-01-01<
/HIREDATE><SAL>2500</SAL><DEPTNO>20</DEPTNO></ROW></ROWSET>


経過: 00:00:00.41


さらに前回までに紹介した、DBMS_XMLGEN.GETXMLやDBMS_XMLGEN.GETXMLTYPE()では関数に渡すクエリーにorder by句を付加すればソートできる。

SCOTT> select
2 dbms_xmlgen.getxml(sqlQuery=>'select * from emp order by empno') xmldoc
3 from dual
4 /

XMLDOC
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>80-12-17</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>81-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>81-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>81-04-02</HIREDATE>
<SAL>2975</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>
<EMPNO>7654</EMPNO>
<ENAME>MARTIN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>81-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>81-05-01</HIREDATE>
<SAL>2850</SAL>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>81-06-09</HIREDATE>
<SAL>2450</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>81-11-17</HIREDATE>
<SAL>5000</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>7844</EMPNO>
<ENAME>TURNER</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>81-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>81-12-03</HIREDATE>
<SAL>950</SAL>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW>
<EMPNO>7902</EMPNO>
<ENAME>FORD</ENAME>
<JOB>ANALYST</JOB>
<MGR>7566</MGR>
<HIREDATE>81-12-03</HIREDATE>
<SAL>3000</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
<JOB>CLERK</JOB>
<MGR>7782</MGR>
<HIREDATE>82-01-23</HIREDATE>
<SAL>1300</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>9999</EMPNO>
<ENAME>ほげ</ENAME>
<JOB>ENGINEER</JOB>
<MGR>7566</MGR>
<HIREDATE>08-01-01</HIREDATE>
<SAL>2500</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
</ROWSET>



経過: 00:00:00.65
SCOTT>


これは書く必要は無かったかもしれないが念のため。
XMLQuery()関数や、XMLTable()関数ではXQueryのFLWOR式でorder by句を使えばソートできる。

SCOTT> select
2 xmlroot(xmldoc.column_value, version '1.0')
3 from
4 xmltable(
5 '<ROWSET>{
6 for $d in ora:view("EMP")
7 order by $d
8 return $d
9 }</ROWSET>'
10 ) xmldoc
11 /

XMLROOT(XMLDOC.COLUMN_VALUE,VERSION'1.0')
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<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>
<ROW>
<EMPNO>9999</EMPNO>
<ENAME>ほげ</ENAME>
<JOB>ENGINEER</JOB>
<MGR>7566</MGR>
<HIREDATE>2008-01-01</HIREDATE>
<SAL>2500</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
</ROWSET>


経過: 00:00:00.30
SCOTT>

次回へつづく。

|

トラックバック


この記事へのトラックバック一覧です: Oracle de XMLDB #22 - Relational dataをXMLへ #5:

コメント

コメントを書く