SQLでXML要素を変更する
ここ数年の間に、表内の列にXMLまたはJSONを格納することが普通になってきました。SQLは、リレーショナル データからXMLまたはJSONを構築する、あるいはXMLまたはJSONをリレーショナル データへ分解するのに必要なすべての機能を提供していますが、要素の内容を変更する簡単な手法は提供していません。この記事では、SQLストアード プロシージャーを使用してXML要素の内容を変更する手法について説明します。
少し背景的な話をするとすれば、近頃、私が関わっていたプロジェクトは、DB2 XML Extender機能を標準XML機能に置き換えるプロジェクトでした。このプロジェクトはDB2 XML ExtenderがIBM i でサポートされなくなったために行われたものです。DB2 XML Extenderには、XML要素または属性の簡単な更新を可能にするUPDATE関数がありましたが、それに相当する簡単な標準SQL関数はありません。IBM Redbook「Replacing DB2 XML Extender with integrated IBM DB2 for i XML capabilities」によると、XSLTRANSFORM関数を使用すれば同じ結果を実現できるとのことですが、そうするには追加の有償製品（XML Toolkit for IBM System i5® - 5733-XT2オプション1および6）およびXSLTテンプレートを使用する必要があります。XML要素に簡単な変更を行うだけにしては、これではやや複雑過ぎるように思われます。そこで私が用いたソリューションは、RPGプログラムを使用することでした。
課題
次のような多くの問題点がありました。
- XMLデータ型のコンテンツを直接操作することは、RPGでは可能ではありません。XML変数は、最初に文字変数へキャストする必要があります。32KというVARCHARの最大サイズでは不十分な場合があるため、CLOBを使用する必要があります。
- 望ましいのはユーザー定義関数（UDF）ですが、残念ながら、SQLでは、RPGプログラムを呼び出しているUDFからCLOBが返されるようにすることはできません。RPGプログラムはストアード プロシージャーとしてラッピングする必要があり、そうすると使い方として少し難しくなります。こうした問題を解決するヒントをご存知の方がいらっしゃれば、私にご連絡ください。
ストアード プロシージャーを使用する
ストアード プロシージャーの使い方を説明するために、2つのグローバル変数を作成する必要があります。
CREATE OR REPLACE VARIABLE TESTXML XML CCSID 1208;
CREATE OR REPLACE VARIABLE TESTCLOB CLOB(1049586); 次いで、XML変数に値を代入します。
set testxml = xmlparse(document 'YouMe');<from>要素の内容を変換するため、まずは、XMLをCLOBにキャストします。
set testclob = xmlserialize(testxml as CLOB(1049586));次いで、<from>要素の内容を置き換えるためのストアード プロシージャーを呼び出します。パラメーターは以下の通りです。
- XMLを格納するCLOB。
- 変更される要素のパス。要素は「/」で区切られます。この例では、<note>要素内の<from>要素を変更します。パス リストの先頭および最後が「/」であるかどうかについて心配する必要はありません。RPGプログラムにより、必要に応じて先行または後続の「/」が追加または除去されます。属性の内容を変更する場合は、要素とは異なり、属性名の前に「@」を付けます。
- 要素の新しい値。
call replaceXMLElement(testclob, 'note/from/', 'Paul');最後に、CLOBをXML変数にキャストし直します。
call replaceXMLElement(testclob, 'note/from/', 'Paul');XML変数の内容を見てみると、
select testxml from sysibm.sysdummy1;XMLが更新されています。
YouPaulRPGプログラム
これが、すべての処理を行うプログラムです。プログラムにはSQL命令はまったく含まれていませんが、SQLデータ型の定義が含まれているため、SQLRPGLEメンバーとして定義される（そしてCRTSQLRPGIを使用してコンパイルされる）必要があります。下記のコードのコールアウトを参照してください。
- SQL CLOBの定義は、結果的にデータ構造の定義になります。この例ではDATACLOBという名前のデータ構造で、サブフィールドDATACLOB_LENおよびDATACLOB_DATAが格納されます。
- パス文字列が必ずスラッシュ（/）で終わり、スラッシュから始まらないようにします。
- DATACLOBデータ構造のサブフィールドを使用して、CLOBの内容を可変フィールドへコピーします。SQLにはVARCHAR変数の32Kというサイズ制限がありますが、RPGでの制限は16Mです。
- 変更されるデータの（CLOB内での）開始位置および長さを検索します。これは要素/属性の内容です。
- 値を変更します。
- 処理しているフィールドをCLOBデータ構造内のサブフィールドにコピーして戻します。
- 要求されたパスで各要素名をループします。要素名を抽出し、それを検索します。
- 要素または属性の開始位置および長さを算出します。
**free
ctl-opt option(*srcstmt: *nodebugIO) dftactgrp(*no);
(1) dcl-s dataCLOB SQLTYPE(CLOB: 1049586) ;
dcl-pr replaceXMLElement extPgm('REPELEM');
CLOBIn likeDS(dataCLOB) ;
searchForElement varChar(3000) const;
replaceWith varChar(1000) const;
end-Pr;
dcl-pi replaceXMLElement;
CLOBIn likeDS(dataCLOB) ;
searchForElement varChar(3000) const;
replaceWith varChar(1000) const;
end-Pi;
dcl-s varForCLOB varChar(1049586: 4) ;
dcl-s searchFor like(searchForElement);
dcl-s elemStart int(10);
dcl-s elemLength int(10);
// ensure path search ends with a / and does not start with /
searchFor = %trim(searchForElement);
if (%len(searchFor) > 0);
(2) if (%subst(searchFor: %len(searchFor): 1) <> '/');
searchFor += '/';
endIf;
if (%subst(searchFor: 1: 1) = '/');
searchFor = %replace('': searchFor: 1: 1);
endIf;
endIf;
// return if no valid CLOB or search element
if (CLOBIn.dataCLOB_len <= 0 or %len(searchFor) <= 0);
return;
endIf;
// copy CLOB contents to varying field
(3) varForCLOB = %subSt(CLOBIn.dataCLOB_data: 1: CLOBIn.dataCLOB_len);
// find the start position and length of data to be changed
(4) findElement(searchFor: varForClob: elemStart: elemLength);
// replace the content of the element
if ((elemStart > 0 and elemStart <= %len(varForCLOB) ) and
elemLength > 0);
(5) varForCLOB = %replace(replaceWith: varForCLOB: elemStart: elemLength);
endIf;
// copy the data back to the CLOB
(6) CLOBIn.dataCLOB_data = varForCLOB;
CLOBIn.dataCLOB_len = %len(varForCLOB);
return;
dcl-proc findElement;
// Find the start position and length of the content of
// the requested element or attribute.
// Scan through the path tree an element at a time
dcl-Pi *n;
searchFor like(searchForElement) const;
varForCLOB varChar(1049586: 4) const;
elemStart int(10);
elemLength int(10);
end-Pi;
dcl-s i int(10) inz(1);
dcl-s j int(10);
dcl-s elementIs varChar(1000);
dcl-s workStart int(10) inz(1);
dcl-s isAttribute ind;
dcl-s fromChar char(1) inz('>');
dcl-s toChar char(1) inz('<');
(7) doU (i = 0 or i >= %len(searchFor));
j = %scan('/': searchFor: i);
if ((j-i) <= 0);
return;
endIf;
elementIs = %subst(searchFor: i: j - i);
isAttribute = (%subst(elementIs: 1: 1) = '@');
if isAttribute;
elementIs = %replace('': elementIs: 1: 1);
fromChar = '"';
toChar = '"';
endIf;
i = j + 1;
workStart = %scan(elementIs: varForCLOB: workStart);
if (workStart = 0);
return;
endIf;
endDo;
monitor;
(8) elemStart = %scan(fromChar: varForCLOB: workStart) + 1;
if (elemStart > 0);
j = %scan(toChar: varForCLOB: elemStart + 1);
endIf;
elemLength = j - elemStart;
on-error;
elemStart = 0;
elemLength = 0;
endMon;
end-Proc;
ストアード プロシージャー
RPGプログラムの用意ができたら、あとは、ストアード プロシージャーを作成するだけです。
CREATE OR REPLACE PROCEDURE REPLACEXMLELEMENT (
INOUT DATACLOB CLOB(1049586) ,
IN SEARCHFOR VARCHAR(3000) ,
IN REPLACEWITH VARCHAR(1000) )
LANGUAGE RPGLE
SPECIFIC REPLACEXMLELEMENT
DETERMINISTIC
NO SQL
CALLED ON NULL INPUT
COMMIT ON RETURN YES
EXTERNAL NAME 'PTARTICLES/REPELEM'
PARAMETER STYLE GENERAL ;便利なユーティリティー
このユーティリティーを何らかのケースでうまく使っていただければと思います。とはいえ、ストアード プロシージャーではなく、ユーザー定義関数としてこれを使用できればもっとよいのに、と思ってしまいます。何かよいアイデアをお持ちの方、ご提案をお待ちしています。
