DB2 Version 10.1 for Linux, UNIX, and Windows

FP1: XML data type support added in global variables and compiled SQL functions

Starting with Version 10.1 Fix Pack 1, you can create global variables of XML data type, you can specify the XML data type in parameters and the RETURNS clause of compiled SQL functions, and you can define local XML variables in compiled SQL functions.

If you are migrating Oracle applications that use XML variables or XML function parameters, this new support can help make that migration easier.

Depending on the size of the XML documents, using XML global variables and XML data type in compiled SQL functions might require additional space in the system temporary table space. You must ensure that there is enough free space in the system temporary table space.

This new capability is available only in DB2® single partition environments.

The following restrictions apply to XML global variables:

You cannot specify a default value other than NULL.
You cannot specify a constant value other than NULL.
You can assign only well-formed documents to XML global variables.

All existing restrictions for compiled SQL function still apply.

Examples

The following example shows how to create an XML global variable:

CREATE OR REPLACE VARIABLE MYSCHEMA.CUSTOMER_HISTORY_VAR XML

The following example shows how to specify an input and output XML parameter when creating a compiled SQL function:

CREATE FUNCTION update_xml_phone
  (IN    regionNo VARCHAR(8),
   INOUT phone_xml XML)
RETURNS VARCHAR(28)
LANGUAGE SQL
NO EXTERNAL ACTION
BEGIN
   DECLARE tmp_full_phone VARCHAR(28);
   SET tmp_full_phone = regionNo || 
            XMLCAST(XMLQUERY('$p/phone' PASSING phone_xml AS "p") AS VARCHAR(20));
   SET phone_xml = XMLELEMENT (NAME "phone", tmp_full_phone);
RETURN tmp_full_phone;
END

The following example shows how to specify XML in the RETURNS clause when creating a compiled SQL function:

CREATE FUNCTION return_phone_number( cid INTEGER)
RETURNS XML
LANGUAGE SQL
NO EXTERNAL ACTION
BEGIN
RETURN 
   SELECT XMLELEMENT (NAME "phone", phone_number) 
     FROM customer WHERE customer_id = cid
END