ネイティブ SQL ルーチンでの XML サポート

ネイティブ SQL ルーチンでは、XML データ・タイプのパラメーターと変数がサポートされています。

SQL ステートメントでは、その他のデータ・タイプの変数と同じ方法で XML パラメーターを使用できます。 また、XMLEXISTS、XMLQUERY、および XMLTABLE 式では、XML データ・タイプの変数をパラメーターとして XQuery 式に渡すことができます。

SQL プロシージャー型言語のストアード・プロシージャー内のリモート・サイトから戻される XML 値は、整形式でなければなりません。

例: ネイティブ SQL プロシージャー ネイティブ SQL プロシージャーでの XML パラメーターと XML 変数の宣言、使用、および割り当ての例を以下のコードに示します。 この例で使用される表 T1 には、XML データ・タイプの列 C1 があります。

CREATE PROCEDURE PROC1(IN PARM1 XML, IN PARM2 VARCHAR(32000))
 LANGUAGE SQL
 BEGIN
  DECLARE var1 XML;                                               1 
  IF(XMLEXISTS('$x/ITEM[value < 200]' passing by ref PARM1 as "x"))THEN
   INSERT INTO T1 VALUES(PARM1);                                  2 
    END IF;
  SET var1 =                                                      3 
   XMLDOCUMENT(XMLELEMENT(NAME "ORDER",
    XMLCONCAT(PARM1, var1)));
  INSERT INTO T1 VALUES(var1);                                    4 
 END #

例: 非インライン SQL 関数 非インライン SQL スカラー関数での XML パラメーターと XML 変数の宣言、使用、および割り当ての例を以下のコードに示します。 この関数は入力パラメーターとして 2 つのパラメーターをとります。このパラメーターは、書籍注文情報と価格 (米ドルまたはカナダドル) が記述されている XML 文書と、通貨為替レートです。 この関数は、カナダ・ドルでの価格を記述した XML 文書を戻します。

CREATE FUNCTION CANOrder(BOOKORDER XML, USTOCANRATE double)
 RETURNS XML
 DETERMINISTIC
 NO EXTERNAL ACTION
 CONTAINS SQL
 BEGIN ATOMIC
  DECLARE USPrice decimal(15,2);
  DECLARE CANPrice decimal(15,2);
  DECLARE OrderInCAN XML;                                     1 
  SET USPrice = XMLCAST(XMLQUERY('/bookorder/USprice' PASSING BOOKORDER)
   AS decimal(15,2));                                         2 
  SET CANPrice = XMLCAST(XMLQUERY('/bookorder/CANprice' PASSING BOOKORDER)
   AS decimal(15,2));                                         3 
  IF CANPrice is NULL or CANPrice <=0 THEN
   IF USPrice >0 THEN
    SET CANPrice = USPrice * USTOCANRATE;
   ELSE
    SET CANPrice = 0;
  END IF;
  SET OrderInCAN =                                            4 
   XMLDOCUMENT(
    XMLELEMENT(NAME "bookorder",
    XMLQUERY('/bookorder/bookname' PASSING BOOKORDER),
    XMLELEMENT(NAME "CANprice", CANPrice))
   );
  RETURN OrderInCAN;
 END#

入力文書の内容が以下のようであるとします。

<bookorder>
 <bookname>TINTIN</bookname>
 <USprice>100.00</USprice>
</bookorder>

為替レートが 1 米ドルあたり 0.9808 カナダ・ドルの場合、出力文書は以下のようになります。

<bookorder><bookname>TINTIN</bookname><CANprice>9.81</CANprice></bookorder>

例: SQL 表関数: SQL 表関数での XML パラメーターと XML 変数の宣言、使用、および割り当ての例を以下のコードに示します。 この関数は入力パラメーターとして 3 つのパラメーターをとります。これらは、注文情報が記述されている XML 文書、注文における最大価格、および注文された書籍のタイトルです。 この関数は、すべての入力パラメーターから生成された領収書が格納されている XML 列と、注文情報文書が指定されている入力パラメーターから取り出された注文 ID が格納される BIGINT 列からなる表を戻します。

CREATE FUNCTION ORDERTABLE
 (ORDERDOC XML, PRICE decimal(15,2), BOOKTITLE varchar(50))
 RETURNS TABLE (RECEIPT XML, ORDERID BIGINT)
 LANGUAGE SQL
 SPECIFIC ORDERTABLE
 NOT DETERMINISTIC
 READS SQL DATA
 RETURN
  SELECT ORDER.RECEIPT, ORDER.ID
   FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://posample.org'),
   '/orderdoc/bookorder[USprice < $A and bookname = $B]'
   PASSING ORDERDOC, PRICE as A, BOOKTITLE as B
   COLUMNS
    ID BIGINT PATH '@OrderID',
    RECEIPT XML PATH '.')
   AS ORDER;

この SQL 表関数は、XMLTABLE 関数を使用して、XMLTABLE 関数から戻される表に対する結果表を生成します。 XMLTABLE 関数は、タイトルが BOOKTITLE 入力パラメーターの書籍タイトルに一致し、価格が PRICE 入力パラメーターの値以下である ORDERDOC 入力文書ごとに行を生成します。 戻される表の列は、ORDERDOC 入力文書の Receipt ノード、および ORDERDOC 入力文書の OrderID エレメントです。

入力パラメーターに以下の値が指定されているとします。

PRICE: 200, BOOKTITLE: TINTIN, ORDERDOC:

<orderdoc xmlns="http://posample.org" OrderID="5001">
 <name>Jim Noodle</name>
 <addr country="Canada">
  <street>25 EastCreek</street>
  <city>Markham</city>
  <prov-state>Ontario</prov-state>
  <pcode-zip>N9C-3T6</pcode-zip>
 </addr>
 <phone type="work">905-555-7258</phone>
 <bookorder>
  <bookname>TINTIN</bookname>
  <USprice>100.00</USprice>
 </bookorder>
</orderdoc>

この場合、次のような表が戻されます。