メニューボタン
IBMi海外記事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;
INVOICEID ROWNUM PRODUCTID PRODUCTNAME
123456 1 111112 Some good stuff
123456 2 111114 Some other good stuff
123457 1 111112 Some good stuff
123457 2 111115 Some more good stuff

F1とF2という、2つの共通表式を定義しました。F1は、インボイスIDを単一列として、すべての製品情報を2つ目の列(ALLROWSという名前を付けました)として取得します。

2つ目のCTE(F2)はF1からInvoiceIDを選択し、また、F1のALLROWS列から製品情報を抽出します。結果は、結合されたヘッダーおよび詳細表を照会することで取得されるものに似ています。F2からすべての列を選択した場合は、XML列を表示することができます。

SQLを使用してネストされたデータをXMLから抽出するには、多少、想像力を働かせる必要はありますが、それは不可能なことではありません。また、困難なことでさえないのです。

あわせて読みたい記事

PAGE TOP