XML support in native SQL routines

Native SQL routines support parameters and variables with the XML data type.

XML parameters can be used in SQL statements in the same way as variables of any other data type. In addition, variables with the XML data type can be passed as parameters to XQuery expressions in XMLEXISTS, XMLQUERY and XMLTABLE expressions.

Start of change An XML value returned from a remote site within an SQL procedural language stored procedure must be well-formed. End of change

Example: native SQL procedure The following code demonstrates the declaration, use, and assignment of XML parameters and variables in a native SQL procedure. The example uses table T1, which has one column named C1, which has the XML data type.

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 #

The SQL procedure performs the following operations on XML parameters and variables:

Declares an XML variable named var1.
Checks whether the value of XML parameter PARM1 contains an item with a value less than 200. If it does, the SQL procedure inserts the XML value into column C1 in table T1.
Concatenates the contents of PARM1 and var1, creates an element named ORDER that contains the concatenated content, returns the ORDER element as a document node, and assigns that document node to XML variable var1.
Inserts the value that is in XML variable var1 into column C1 in table T1.

Example: non-inline SQL function The following code demonstrates the declaration, use, and assignment of XML parameters and variables in a non-inline SQL scalar function. This function takes two parameters as input: an XML document that contains book order information, with prices in U.S. dollars or Canadian, and the monetary exchange rate. The function returns an XML document that contains the prices in Canadian dollars.

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#

The SQL function performs the following operations:

Declares an XML variable named OrderInCAN, which will hold the order with prices in Canadian dollars that is returned to the caller.
Retrieves the U.S. price from the input document, which is in the BOOKORDER parameter.
Looks for a Canadian price in the input document. If the document contains no Canadian prices, the XMLCAST function on the XMLQUERY function returns NULL.
Builds the output document, whose top-level element is bookorder, by concatenating the bookname element from the original order with a CANprice element, which contains the calculated price in Canadian dollars.

Suppose that an input document looks like this:

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

If the exchange rate is 0.9808 Canadian dollars for one U.S. dollar, the output document looks like this:

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

Example: SQL table function: The following code demonstrates the declaration, use, and assignment of XML parameters and variables in an SQL table function. This function takes three parameters as input: an XML document that contains order information, a maximum price for the order, and the title of the book that is ordered. The function returns a table that contains an XML column with receipts that are generated from all of the input parameters, and a BIGINT column that contains the order IDs that are retrieved from the input parameter that contains the order information document.

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;

The SQL table function uses the XMLTABLE function to generate the result table for the table that is returned by the function. The XMLTABLE function generates a row for each ORDERDOC input document in which the title matches the book title in the BOOKTITLE input parameter, and the price is less than the value in the PRICE input parameter. The columns of the returned table are the Receipt node of the ORDERDOC input document, and the OrderID element from the ORDERDOC input document.

Suppose that the input parameters have these values:

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>

The returned table looks like this:

ID	RECEIPT
5001	<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>