SQLを使用してネストされたXMLを読み取る｜e-bellnet.com（IBM i （AS/400）ユーザーのための関連技術情報サイト）

IBM i 資料集

IBMｉ海外記事2020.10.28

SQLを使用してネストされたXMLを読み取る

Jonathan M. Heinz 著

XMLは、データ交換フォーマットであって、リレーショナルデータベース管理システムではありません。そのため、SQLを使用してXMLデータを照会することは大変な場合もあります。2つのリレーショナル表に保存されるだろうデータが、XMLの1つの要素に置かれるためです。別の言い方をすれば、詳細データはヘッダーデータの下でネストされているということです。

この記事では、SQLを使用してXMLファイルからネストされたデータを抽出する方法について紹介しようと思います。顧客に送られるXMLを作成するプロセスの変更をテストしたときに、この方法が有用だったことがありました。このSQLは、プロセスが正確にXMLを取り込んでいることを素早くチェックするのに使用することができます。

そのXMLには、詳細な製品行を持つインボイス情報が格納されていました。インボイス番号ごとに製品行を抽出する必要がありました。一見すると、これは簡単なはずですが、反復される製品行のために、簡単ではありません。

以下は、複数のネストされた要素を持つXMLの簡略化された例です。2つの顧客に送られる2つのインボイスがあり、インボイスにはそれぞれ2つの製品行が含まれています。現実には、何千ものインボイスが含まれていたり、それぞれのインボイスに何千もの製品行があったりするケースもあります。

<?xml version='1.0' encoding='UTF-8'? >
<INVOICES>
  <INVOICE>
    <HEADER>
     <INVOICE_ID>123456</INVOICE_ID>
       <CURRENCY>
           <CODE>EUR</CODE>
       </CURRENCY>
    </HEADER>
    <RECEIVER>
       <CUSTOMER_INFORMATION>
          <CUSTOMER_NAME>COMPANY1</CUSTOMER_NAME>
          <CUSTOMER_ID>00000001</CUSTOMER_ID>
          <ADDRESS>
             <STREET_ADDRESS1>Lane 1</STREET_ADDRESS1>
             <STREET_ADDRESS2></STREET_ADDRESS2>
             <STREET_ADDRESS3></STREET_ADDRESS3>
             <POSTAL_CODE>10</POSTAL_CODE>
             <COUNTRY>UK</COUNTRY>
          </ADDRESS>
       </CUSTOMER_INFORMATION>
    </RECEIVER>
    <ROWS>
       <ROW>
       <ROW_NUMBER>1</ROW_NUMBER>
           <PRODUCT>
              <PRODUCT_ID>111112</PRODUCT_ID>
              <PRODUCT_NAME>Some good stuff</PRODUCT_NAME>
          </PRODUCT>
           <ROW_TOTAL>
             <AMOUNT SIGN="+" VAT="EXCLUDED">10.000</AMOUNT>
           </ROW_TOTAL>
       </ROW>
       <ROW>
       <ROW_NUMBER>2</ROW_NUMBER>
           <PRODUCT>
             <PRODUCT_ID>111114</PRODUCT_ID>
             <PRODUCT_NAME>Some other good stuff</PRODUCT_NAME>
         </PRODUCT>
           <ROW_TOTAL>
             <AMOUNT SIGN="+" VAT="EXCLUDED">5.350</AMOUNT>
           </ROW_TOTAL>
       </ROW>
    </ROWS>
  </INVOICE>
  <INVOICE>
    <HEADER>
     <INVOICE_ID<123457</INVOICE_ID>
       <CURRENCY>
           <CODE>EUR</CODE>
       </CURRENCY>
    </HEADER>
    <RECEIVER>
      <CUSTOMER_INFORMATION>
          <CUSTOMER_NAME>COMPANY2</CUSTOMER_NAME>
          <CUSTOMER_ID>00000002</CUSTOMER_ID>
          <ADDRESS>
             <STREET_ADDRESS1>Lane 2</STREET_ADDRESS1>
             <STREET_ADDRESS2></STREET_ADDRESS2>
             <STREET_ADDRESS3></STREET_ADDRESS3>
             <POSTAL_CODE>20</POSTAL_CODE>
             <COUNTRY>UK</COUNTRY>
          </ADDRESS>
      </CUSTOMER_INFORMATION>
    </RECEIVER>
    <ROWS>
       <ROW>
       <ROW_NUMBER>1</ROW_NUMBER>
           <PRODUCT>
              <PRODUCT_ID>111112</PRODUCT_ID>
              <PRODUCT_NAME>Some good stuff</PRODUCT_NAME>
          </PRODUCT>
           <ROW_TOTAL>
             <AMOUNT SIGN="+" VAT="EXCLUDED">10.000</AMOUNT>
           </ROW_TOTAL>
       </ROW>
       <ROW>
       <ROW_NUMBER>2</ROW_NUMBER>
           <PRODUCT>
              <PRODUCT_ID>111115</PRODUCT_ID>
              <PRODUCT_NAME>Some more good stuff</PRODUCT_NAME>
           </PRODUCT>
           <ROW_TOTAL>
             <AMOUNT SIGN="+" VAT="EXCLUDED">5.350</AMOUNT>
           </ROW_TOTAL>
       </ROW>
    </ROWS>
  </INVOICE>
</INVOICES>

Access Client Solutions（ACS）で、「SQLスクリプトの実行」を使用して、以下の照会を実行しました。XMLでの作業では、コミットメント制御を使用する必要があります。使用していない場合、システムは、SQL状態42926（LOBおよびXMLロケーターは、COMMIT(*NONE) とともには使用できません。）で応答します。接続がコミットメント制御を使用するように設定されていない場合は、照会を実行する前に次のコマンドを発行してください。

set transaction isolation level read committed;

インボイスIDおよび顧客情報（ヘッダーデータ）の抽出は、以下のSQL照会で簡単に行えます。

select a.* from xmltable('INVOICES/INVOICE'
  passing (xmlparse(document get_xml_file('/SomeDir/INVOICE.XML')))
  columns
    InvoiceID varchar(6) Path 'HEADER/INVOICE_ID',
    CustomerName varchar(20) Path 'RECEIVER/CUSTOMER_INFORMATION/CUSTOMER_NAME',
    CustomerNumber varchar(10) Path 'RECEIVER/CUSTOMER_INFORMATION/CUSTOMER_ID'
 ) as a;

INVOICEID	CUSTOMERNAME	CUSTOMERNUMBER
123456	COMPANY1	00000001
123457	COMPANY2	00000002

これは非常に分かりやすいと思います。それでは、すべての製品行が返されるようにしたい場合はどうでしょうか。これも非常に簡単です。

select a.* from xmltable ('INVOICES/INVOICE/ROWS/ROW'
 Passing ( xmlparse(document get_xml_file('/SomeDir/INVOICE.XML')))
 columns
    ROWNUM  integer  Path 'ROW_NUMBER',
    PRODUCTID varchar(20) Path 'PRODUCT/PRODUCT_ID',
    PRODUCTNAME varchar(50) Path 'PRODUCT/PRODUCT_NAME'
 ) as a;

ROWNUM	PRODUCTID	PRODUCTNAME
1	111112	Some good stuff
2	111114	Some other good stuff
1	111112	Some good stuff
2	111115	Some more good stuff

では、それぞれの製品が属しているインボイスのIDを知りたい場合はどうでしょうか。これは問題を引き起こします。製品が繰り返しグループであるため、最初の照会に製品IDと製品名を追加するだけでは済みません。データベースは、SQL状態10507で応答します（XPath式のタイプが、その式が発生したコンテキストに対して無効です）。

この障害を回避する1つの方法は、XMLデータタイプでCTE（共通表式）を使用する方法です。

with f1 as
   (select *
       from xmltable ('INVOICES/INVOICE'
             passing ( xmlparse(document get_xml_file('/SomeDir/INVOICE.XML')))
             columns
                InvoiceID   varchar(6)   Path 'HEADER/INVOICE_ID',
                ALLROWS     xml          Path 'ROWS')),
f2 as
   (select f1.*, p.*
      from f1,    
           xmltable ('ROWS/ROW' 
            passing ALLROWS
            columns 
               ROWNUM   integer   Path 'ROW_NUMBER',
               PRODUCTID varchar(20)  Path 'PRODUCT/PRODUCT_ID',
               PRODUCTNAME varchar(50) Path 'PRODUCT/PRODUCT_NAME') as P)
select f2.InvoiceID, f2.RowNum, f2.ProductID, f2.ProductName from f2;