DB2 Version 10.1 for Linux, UNIX, and Windows

Parameters and variables of data type XML in SQL functions

DB2® database systems support the XML data type for inlined SQL functions that you create using the CREATE FUNCTION (SQL scalar, table, or row) statement or the CREATE FUNCTION (sourced or template) statement.

In an inlined user-defined function created with the CREATE FUNCTION (SQL scalar, table or row) statement, you can use XML variables in SQL statements in the same way as variables of any other data type. For example, in a user-defined function you can pass variables of data type XML as parameters to XQuery expressions in an XMLEXISTS predicate or a function like XMLQUERY or XMLTABLE.

In a user-defined function created with the CREATE FUNCTION (sourced or template) statement where the source function is a user-defined SQL scalar function, you can use the XML data type as an input, output, or input/output parameter.

XML values are assigned by reference in a user-defined function.

Parameters and variables of data type XML are not supported in compiled SQL functions.

Example

The following example function is an inline SQL scalar function that uses the XML data type as an input parameter and a variable. The function extracts the phone number element from an XML document using an XQuery expression and returns the phone number element:

CREATE FUNCTION phone_number ( dept_doc XML )
RETURNS XML
LANGUAGE SQL
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE tmp_xml XML;
 IF (XMLEXISTS('$test/department/phone' passing by ref dept_doc as "test"))
 THEN
  SET tmp_xml = XMLQUERY('document 
       {<phone_list>{$doc/department/phone}</phone_list>}' 
    PASSING dept_doc as "doc");
 ELSE
 SET tmp_xml = XMLPARSE(document '<phone_list><phone>N/A</phone></phone_list>');
END IF;
RETURN tmp_xml;
END

The following SELECT statement uses the PHONE_NUMBER function to retrieve the phone number from an XML document in a table with employee information.

SELECT PHONE_NUMBER(info) FROM employees WHERE empid = 12356

The SELECT statement assumes the table is similar to a table created with the following CREATE TABLE statement and contains data similar to the information inserted with following INSERT statement:

CREATE TABLE employees (empid BIGINT, info XML )

INSERT INTO EMPLOYEES VALUES ( 12356, ' 
    <department id="marketing">
        <empid>12356</empid>
        <phone>555-123-4567</phone>
     </department> ')

Using the previous table and information, The SELECT statement returns the following phone number information:

<phone_list><phone>555-123-4567</phone></phone_list>