Oracle de XMLDB #26 - XMLTYPEの更新 Tweet
OTN-Jで、PL/SQLでXMLTYPEのあるの要素の値を変更したり、ある要素の値を参照したいというスレッドがあったのでレスしておいた。
そういえば、XQueryやXPathを使ったOracle XMLDBネタもやっているが更新系のはやってないな（というかまだ、XMLの更新、追加、削除に関する標準が定まっていないわけだから仕方ない。）
ということで、前述したOTN-Jのネタをもう少しフラッシュアップして？！(for updateなどが抜けていたので。。）残しておく事にした。
まず、hogehoge表に以下のようなXMLを登録する。
SCOTT> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SCOTT>
SCOTT> desc hogehoge
名前 NULL? 型
----------------------------------------- -------- ----------------------------
SEQNO NOT NULL NUMBER
XMLINFO XMLTYPE
SCOTT>
SCOTT> insert into hogehoge values(1,xmltype('<?xml version="1.0"?><addressbook>
<person id="1"><name>岡田</name><age>30</age>
<address zipcode="123456">東京都ホゲ市ホゲ町１−１</address></person><person id="2">
<name>安倍</name><age>50</age>
<address zipcode="1050011">東京都ホゲ区ホゲ田町１−１−１</address>
</person></addressbook>'));
1行が作成されました。
SCOTT> commit;
コミットが完了しました。
SCOTT>
SCOTT>
SCOTT> set timi on
SCOTT> set long 4000
SCOTT> col xmlinfo for a80
SCOTT> set linesize 132
SCOTT> set pagesize 1000
SCOTT> select * from hogehoge where seqno=1;
SEQNO XMLINFO
---------- ------------------------------------------------------------
1 <?xml version="1.0"?><addressbook><person id="1"><name>岡田<
/name><age>30</age><address zipcode="123456">東京都ホゲ市ホ
ゲ町１−１</address></person><person id="2"><name>安倍</name>
<age>50</age><address zipcode="1050011">東京都ホゲ区ホゲ田
町１−１−１</address></person></addressbook>
経過: 00:00:00.01
SCOTT>
上記のようなXML文書を対象に特定のid属性値を持つperson要素に含まれるname要素の値を変更するストアドファンクション。
（もうすこし平たく言うと、指定した名前を変更するストアドファンクション。略し過ぎか・・・）
XML文書の変更方法は他にもあるが、PL/SQLパッケージで行うにはDOM APIであるDBMS_XMLDOMパッケージを利用して行う。
尚、XMLTYPEをDBMS_XMLDOMパッケージで更新する際のポイントは、XMLTYPEインスタンスからDOMインスタンスを生成し、DOMインスタンに対して必要な更新操作行った上で、XMLTYPEインスタンスを表へ書き戻すという所。（該当部分は赤太字にしてあります。）
SCOTT> l
1 create or replace
2 function changePersonName
3 (
4 iKey in number,
5 iPersonId in varchar2,
6 iNewName in varchar2
7 ) return xmltype
8 is
9 xml xmlType;
10 personNodeList dbms_xmldom.DomNodeList;
11 personNode dbms_xmldom.DomNode;
12 nameNodeList dbms_xmldom.DomNodeList;
13 personAttrNodeMap dbms_xmldom.DomNamedNodeMap;
14 idAttrNode dbms_xmldom.DomNode;
15 wKey number;
16 wPersonId varchar2(38);
17 wNewName varchar2(60);
18 begin
19 wKey := iKey;
20 wPersonId := iPersonId;
21 wNewName := iNewName;
22
23 select xmlinfo into xml from hogehoge where seqno = wKey for update;
24
25 personNodeList := dbms_xmldom.getElementsByTagName(
26 dbms_xmldom.newDomDocument(xml)
27 ,'person'
28 );
29
30 if not dbms_xmldom.isNull(personNodeList) then
31 for i in 0..dbms_xmldom.getLength(personNodeList) loop
32 personNode := dbms_xmldom.item(personNodeList, i);
33 personAttrNodeMap := dbms_xmldom.getAttributes(personNode);
34 idAttrNode := dbms_xmldom.getNamedItem(personAttrNodeMap, 'id');
35 if dbms_xmldom.getNodeValue(idAttrNode) = wPersonId then
36 nameNodeList := dbms_xmldom.getChildrenByTagName(
37 dbms_xmldom.makeElement(personNode)
38 ,'name'
39 );
40 dbms_xmldom.setNodeValue(
41 dbms_xmldom.getFirstChild(dbms_xmldom.item(nameNodeList, 0))
42 ,wNewName
43 );
44 update hogehoge set xmlinfo = xml where seqno = wKey;
45 exit;
46 end if;
47 end loop;
48 end if;
49 return xml;
50* end;
SCOTT> /
ファンクションが作成されました。
経過: 00:00:00.04
SCOTT>
SCOTT>
SCOTT>
SCOTT> l
1 declare
2 xml xmlType;
3 begin
4 xml := changePersonName(1, '1', '福田ほげ蔵');
6 commit;
7* end;
SCOTT> /
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:00.03
SCOTT>
SCOTT>
SCOTT> select * from hogehoge where seqno=1;
SEQNO XMLINFO
---------- --------------------------------------------------------------------------------
1 <?xml version="1.0"?>
<addressbook>
<person id="1">
<name>福田ほげ蔵</name>
<age>30</age>
<address zipcode="123456">東京都ホゲ市ホゲ町１−１</address>
</person>
<person id="2">
<name>安倍</name>
<age>50</age>
<address zipcode="1050011">東京都ホゲ区ホゲ田町１−１−１</address>
</person>
</addressbook>
経過: 00:00:00.00
SCOTT>
もうお気づきだと思うが、Oracle11gでも発生しているこちらからは操作できないところでwhitespaceが追加されたり、されなかったりする。まあこれ自体は大抵の場合問題にはならないのだが。。
あともうひとつの話題は特定の要素の値を取り出したいということ。これだけならDBMS_XMLDOMパッケージを使わなくても、SQL/XMLを使えば簡単ですよね。
XMLQuery()関数を使って、id属性値が "1"であるperson要素以下にあるage要素の値を参照する例です。
SCOTT> set linesize 80
SCOTT> l
1 select
2 xmlquery(
3 'let $n := $xmldoc//person[@id="1"]/age/text()
4 return $n'
5 passing by value xmlinfo as "xmldoc"
6 returning content
7 ) as "age"
8* from hogehoge where seqno=1
SCOTT> /
age
--------------------------------------------------------------------------------
30
経過: 00:00:00.04
SCOTT>
ちなみに、Oracleのマニュアルは以下の辺りを参照するとよいと思いますが、DOM、XQueryやXPathに関してはXML関連のサイトもググって調べたほうがよいと思います。XMLの操作の基本を理解していれば、あとはどんなツールを使ってそれを行うかということだけですから。
マニュアル
Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス 10g R2 - DBMS_XMLDOM
Oracle Database SQLリファレンス 10g R2 - XMLQUERY
Enjoy!
| 固定リンク | 0
コメント