Oracle Databaseのテーブル内にある文字列形式のXMLデータを、SQL(PL/SQLでも可)で抽出・操作する方法について書きます。
Oracle Database 9i以降に組み込まれたOracle XML DBと呼ばれるテクノロジにより、XMLの処理が簡単にできるようになりました。
なお、今回の動作確認は、12cで行っています。
Oracle XML DBとは?
公式マニュアルによると、以下の通り。
Oracle XML DBは、XMLデータの格納、生成、アクセス、検索、検証、変換、拡張および索引付けなどの高パフォーマンスの処理に関連する一連のOracle Databaseテクノロジです。
1.1 Oracle XML DBの概要
要するに、Oracle Database内にあるXML形式のデータを処理するのに使う。
データが文字列型(VARCHAR2でもCLOBでも)のXMLデータも当然対象として扱えます。
抽出 (SELECT) の仕方
VARCHAR2とかCLOBとか文字列型のままではXMLとして処理できないので、独自のXMLType型に変換してやる必要があります。
XMLTYPE.CREATEXML(文字列)
SELECT
XMLTYPE.CREATEXML('<?xml version="1.0" encoding="UTF-8" ?><test>Hello!</test>') AS XML
FROM DUAL
XMLTYPE(文字列) としても同じようです。
SELECT
XMLTYPE('<?xml version="1.0" encoding="UTF-8" ?><test>Hello!</test>') AS XML
FROM DUAL
ノードが存在するかどうかを調べる(EXISTSNODE)
XPath式を使います。
XPathはOracle固有のものではなく、XMLのノード検索のための標準的な構文なので、Wikipediaなどを参照のこと。
例えば、以下のようなXMLがあるとします。
<?xml version="1.0" ?>
<Document>
<Child1></Child1>
<Child2></Child2>
<Child2></Child2>
</Document>
これに対し、下のSQLを実行するとChild2は存在しますので、存在する場合の値である1が返されます。
ノードが存在しない場合はゼロ、XMLデータとなる文字列もしくはXPath文字列がNULLの場合はNULLが返されます。
SELECT
XMLTYPE.CREATEXML('<?xml version="1.0" ?>
<Document>
<Child1></Child1>
<Child2></Child2>
<Child2></Child2>
</Document>
').EXISTSNODE('/*/Child2') AS XML
FROM DUAL;
特定のノードを抽出する (EXTRACT)
EXTRACTファンクションを使い、XPath式で指定します。
以下のXMLに対し、
<?xml version="1.0" ?>
<Document>
<Child1>Dog</Child1>
<Child2>Cat</Child2>
<Child2>Mouse</Child2>
</Document>
下のようなSELECT文で抽出すると、文字列型の”Dog”が値として返ってきます。
EXTRACTファンクションは、戻り値としてXMLTypeインスタンスを返すので、GETSTRINGVALファンクションで文字列型に変換しています。
SELECT
XMLTYPE.GETSTRINGVAL(
XMLTYPE.CREATEXML('<?xml version="1.0" ?>
<Document>
<Child1>Dog</Child1>
<Child2>Cat</Child2>
<Child2>Mouse</Child2>
</Document>
').EXTRACT('Document/Child1/text()')) AS STRINGVAL
FROM DUAL;
![](/archive2019/wp-content/uploads/2020/06/image-120.png)
XMLの抽出は、この2つがあれば恐らくできるのではないでしょうか。
他のファンクションに、CLOBを返すGETCLOBVAL、NUMBER型を返すGETNUMBERVALなどがあります。
公式ドキュメントが詳しいので、知りたい方はそちらを当たって下さい。
・XMLTYPE – Oracle Database PL/SQLパッケージおよびタイプ・リファレンス (12.1)
以上です。
終わり。