SQLを使用してXMLを生成する－簡単な方法｜e-bellnet.com（IBM i （AS/400）ユーザーのための関連技術情報サイト）

メニューボタン

HOME サポートチーム便り IBM i 海外記事 IBM i コラム

IBM i 資料集

BELLNETコミュニティ

IBMｉ海外記事2023.11.08

SQLを使用してXMLを生成する－簡単な方法

Bob Cozzi 著

XMLを生成する方法には、以下のように、多くの冗長な方法があります。

独自のRPGコードを書く
フリーまたはサードパーティのAPIを使用する
SQL iQueryのOUTPUT(*XML)オプションを使用する
XMLELEMENTなどの組み込みSQL XML関数を使用する

他にもあるでしょうが、最もよく使用されているのは、これらであるように思われます。

私は、長年、XMLELEMENTアプローチを使用していました。これは、Db2 for i SQLに組み込まれている、かなり冗長なXML関数のセットです。実のところ、SQL iQueryのOUTPUT(*XML)は、その機能をベースにしています。OUTPUT(*XML)は、出力列/フィールド名ごとに、XMLELEMENTステートメントを使用してSQLステートメントを再生成しているだけです。

しかし、IBM ACS RUNSQLを使用してSQLステートメントを実行するか、またはRPG IVプログラムにルーチンを組み込む場合は、もっとイライラが少なくて済むアプローチがあります。

XMLROW

XMLROW関数は、標準のSQL列の内容の代わりに、そのデータに対するXMLステートメントを返します。たとえば、CUSTNAMEという名前のデータベースファイル内のCustomer Name（顧客名）列は、以下のように返されます。

独自のXML生成プログラムを書き続けているRPG IVプログラマーが多いのには驚かされています。ファイル仕様書および外部記述データを使用する場合は、そのアプローチではいくぶん「行き詰まり」だと思います。

ワークステーションまたはPRINTERファイル以外では、私は少なくとも20年間、RPG IVでF仕様書は使用していません。

ほとんどの人々は、そうした一度に1行/レコードの出力要件があるか、XMLにデータセット全体をダンプするかのいずれかです。両方のオプションは、XMLROWを介したSQLからXMLへの変換を使用して行うことができ、やはり、構文は非常に単純です。

それでは、ほとんどのIBM i システムに付属しているデモデータベースファイルを使用してみましょう。QIWSライブラリーのQCUSTCDTファイルです。このファイルには12件のレコードが格納されており、「SELECT *」でダンプすると、以下のようになります。

これをXML出力へ送るには、XMLROWでSELECTステートメントの列を囲むだけです。ただし、「SELECT *」派の方にとっては、いつもの慣れているやり方に比べると、多少手間が増えることになるでしょうが、非常に賢明なSQLの導師がかつて述べたように、「動的照会を除いて、SELECT *は使うべからず」です。まず、この照会を拡張して、XMLドキュメントに含めたい列名を明示的にコーディングしてみましょう。ここでは、顧客番号、氏名、および住所のみを含めることとします。

SELECT
     cusnum as "CUSNUM", 
     TRIM(LSTNAM) as "LASTNAME",
     TRIM(INIT) as "INIT",
     TRIM(STREET) as "ADDRESS", 
     CITY as "CITY",
     STATE as "STATE",
     cast(digits(ZIPCOD) as varchar(6)) as "ZIPCODE"
  FROM qiws.qcustcdt;

SQL iQueryまたはIBM ACS RUNSQLを使用したこのステートメントからの出力は、上述の元の結果と基本的に同じですが、含まれるデータの列は少なくなります（郵便番号の後で終了）。

ここからが面白いところです。このステートメントに、この結果をXMLドキュメントとして出力させてみましょう。そうするためには、列を囲むようにXMLROW関数を埋め込む必要があります。実際的には、XMLROWで、SELECT節の後からFROM節の前にあるものをすべて囲むということです。次のようにします。

SELECT
  xmlrow(
     cusnum as "CUSNUM", 
     TRIM(LSTNAM) as "LASTNAME",
     TRIM(INIT) as "INIT",
     TRIM(street) as "ADDRESS", 
     CITY as "CITY",
     STATE as "STATE",
     cast(digits(ZIPCOD) as varchar(6)) as "ZIPCODE"
   )
  FROM qiws.qcustcdt;

「SELECT」キーワードの直後に「XMLROW(」が埋め込まれ、ZIPCOD列用の「ZIPCODE」ラベルの直後に閉じ丸括弧が置かれているのがお分かりでしょうか。

これだけです。これで、整形式のXMLドキュメントが出力されるようになります。以下はその抜粋です。

<row>
 <CUSNUM>938472</CUSNUM>
 <LASTNAME>Henning</LASTNAME>
 <INIT>G K</INIT>
 <ADDRESS>4859 Elm Ave</ADDRESS>
 <CITY>Dallas</CITY>
 <STATE>TX</STATE>
 <ZIPCODE>75217</ZIPCODE>
</row>

それぞれの行にはコンテナが必要であるため、XMLROWは「row」というデフォルトのコンテナノード名を挿入します。最後の列の後にOPTION ROW節を指定することによって、これを上書きすることができます。次のようにします。

SELECT
  xmlrow(
     cusnum as "CUSNUM", 
     TRIM(LSTNAM) as "LASTNAME",
     TRIM(INIT) as "INIT",
     TRIM(street) as "ADDRESS", 
     CITY as "CITY",
     STATE as "STATE",
     cast(digits(ZIPCOD) as varchar(6)) as "ZIPCODE"
   OPTION ROW "CUSTOMER")
FROM qiws.qcustcdt;

これで、それぞれの列ノードのセットを囲むように、ノードが挿入され、デフォルトで挿入されるノードが置換されます。

もうひとつ、XMLROW関数に関するオプションがあります。すなわち、「AS ATTRIBUTES」節です。これを指定すると、XMLROWは、行ごとに1つのタグ/ノードを作成します。タグ/ノードはその前とまったく同じ名前が指定されます。つまり、デフォルトでは、「row」という名前が指定され、そうでない場合は、ユーザー指定の名前が付けられます。ただし、それぞれの列は、XML属性として生成されるため、前述の1番目の行は、次のように生成されることになります。

特定のニーズによっては、AS ATTRIBUTESは、強力なオプションになるかもしれません。

ほとんどの環境では、XMLデータのセット全体も、1つのより大きな外側のノードで囲まれる必要があります。SQL iQueryのOUTPUT(*XML)には、この機能をサポートするXMLOPTパラメーターがありますが、ネイティブのXMLには、それを行う簡単な方法があるようには思われません。そこで、私のお勧めはこうです。

SQLを使用してXMLを生成していて、そのXMLがIFSファイルに保管される必要がある場合は、IFS APIを使用してデータを書き出すか、IFS_WRITE SQLストアードプロシージャーを使用する必要があります。どちらのケースでも、出力ルーチンを呼び出してから、IFSファイルにXMLコンテンツをすべて書き出した後でXMLコンテンツを送信するだけで、最も外側のノードラッパーでXMLを囲むことができます。そのようなことについては、以下のRPG IVのコード例に示されています。このコードは、私のGitHubページでも公開しています。

ただそれだけのことです。

           ctl-opt dftactgrp(*NO);

           // ------------------------------------------------------
           // How to generate XML from Db2 and save that XML content
           // to the IFS as an ASCII text file.
           // ------------------------------------------------------

           dcl-s  content  SQLTYPE(CLOB:65532);
           dcl-s  start    int(10);
           dcl-s  ifsXMLFile varchar(1024) INZ('/home/<USRPRF>/DEMO.XML');
           dcl-s  ifsUser  varchar(10) INZ(*USER);

           dcl-s parentNode varchar(16) inz('CUSTOMERS>');

           exec SQL SET OPTION commit=*NONE, NAMING=*SYS;
            *INLR = *ON;

            EXEC SQL DECLARE XC CURSOR for
               SELECT
                 xmlrow(
                    cusnum as "CUSNUM",
                    TRIM(LSTNAM) as "LASTNAME",
                    TRIM(INIT) as "INIT",
                    TRIM(street) as "ADDRESS",
                    CITY as "CITY",
                    STATE as "STATE",
                    cast(digits(ZIPCOD) as varchar(6)) as "ZIPCODE"
                  OPTION ROW "CUSTOMER" )
              FROM QIWS.QCUSTCDT;

             EXEC SQL OPEN XC;

               // Read XML into a CLOB or you'll have a learning experience.
             EXEC SQL FETCH XC INTO :content;

             if (SQLState < '02000');
             ifsXMLFile  = %SCANRPL('<USRPRF>' : %TrimR(ifsUser) : ifsXMLFile);
                // write out the starting/opening node to the IFS file
               EXEC SQL call qsys2.ifs_write_UTF8( :ifsXMLFile,
                                                   '<' concat :parentNode );
               DOW (SQLState < '02000');
                  // XMLROW returned via RPG IV SQL FETCH adds the <?xml...> tag
                  // We don't want that, so we skip past it using POSITION and SUBSTR
                 EXEC SQL VALUES POSITION('<CUSTOMER>', :content) INTO :START;
                 EXEC SQL call qsys2.ifs_write_UTF8( :ifsXMLFile,
                                                    substr(:content,:start));
                 EXEC SQL FETCH XC INTO :content;
               enddo;
                // write out the ending/closing node to the IFS file
               EXEC SQL call qsys2.ifs_write_UTF8( :ifsXMLFile,
                                                   '</' concat :parentNode );
             endif;
             EXEC SQL CLOSE XC;