2019.04.11



Paul Tuohy著

SQLでXML要素を変更する

Pick Up

ここ数年の間に、表内の列に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が更新されています。

YouPaul

RPGプログラム

これが、すべての処理を行うプログラムです。プログラムにはSQL命令はまったく含まれていませんが、SQLデータ型の定義が含まれているため、SQLRPGLEメンバーとして定義される(そしてCRTSQLRPGIを使用してコンパイルされる)必要があります。下記のコードのコールアウトを参照してください。

  1. SQL CLOBの定義は、結果的にデータ構造の定義になります。この例ではDATACLOBという名前のデータ構造で、サブフィールドDATACLOB_LENおよびDATACLOB_DATAが格納されます。
  2. パス文字列が必ずスラッシュ(/)で終わり、スラッシュから始まらないようにします。
  3. DATACLOBデータ構造のサブフィールドを使用して、CLOBの内容を可変フィールドへコピーします。SQLにはVARCHAR変数の32Kというサイズ制限がありますが、RPGでの制限は16Mです。
  4. 変更されるデータの(CLOB内での)開始位置および長さを検索します。これは要素/属性の内容です。
  5. 値を変更します。
  6. 処理しているフィールドをCLOBデータ構造内のサブフィールドにコピーして戻します。
  7. 要求されたパスで各要素名をループします。要素名を抽出し、それを検索します。
  8. 要素または属性の開始位置および長さを算出します。
    **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 ;

便利なユーティリティー

このユーティリティーを何らかのケースでうまく使っていただければと思います。とはいえ、ストアード プロシージャーではなく、ユーザー定義関数としてこれを使用できればもっとよいのに、と思ってしまいます。何かよいアイデアをお持ちの方、ご提案をお待ちしています。

ページトップ

ボタン