Starting with Version 9.7 Fix Pack 6, 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.
All existing restrictions for compiled SQL function still apply.
CREATE OR REPLACE VARIABLE MYSCHEMA.CUSTOMER_HISTORY_VAR XML
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
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