XMLFOREST scalar function

The schema is SYSIBM.

xmlnamespace-function

Specifies the XML namespace declarations that are the result of the XMLNAMESPACES function. The namespaces that are declared are in the scope of the XMLFOREST function. The namespaces apply to any nested XML functions within the XMLFOREST function, regardless of whether or not those functions appear inside another subselect. See XMLNAMESPACES scalar function for more information on declaring XML namespaces.

If xmlnamespace-function is not specified, namespace declarations are not associated with the constructed sequence of XML element nodes.

element-content-expression

Specifies an expression that returns a value that is used for the content of a generated XML element. The result of the expression is mapped to an XML value according to the mapping rules from an SQL value to an XML value. If the expression is not a simple column reference, element-name must be specified.

AS element-name

Specifies an identifier that is used for the XML element name.

An XML element name must be an XML QName. If the name is qualified, the namespace prefix must be declared within the scope.

If element-name is not specified, element-content-expression must be a column name. The element name is created from the column name using the fully escaped mapping from a column name to a QName.

OPTION

Specifies options for the result for NULL values, binary data, and bit data. The options will not be inherited by the XMLELEMENT or XMLFOREST functions that appear in element-content-expression.

EMPTY ON NULL or NULL ON NULL

Specifies if a null value or an empty element is returned when the values of each element-content-expression is a null value. EMPTY ON NULL and NULL on NULL only affect null handling of the element-content-expression arguments, not the handling of values from an xmlattributes-function argument.

EMPTY ON NULL

If the value of each element-content-expression is null, an empty element is returned.

EMPTY ON NULL is the default.

NULL ON NULL

If the value of each element-content-expression is null, a null value is returned.

XMLBINARY USING BASE64 or XMLBINARY USING HEX

Specifies the assumed encoding of binary input data, character string data with the FOR BIT DATA attribute, ROWID, or a distinct type that is based on one of these types. The encoding applies to element content or attribute values.

XMLBINARY USING BASE64

Specifies that the assumed encoding is base64 characters, as defined for XML schema type xs:base64Binary encoding. The base64 encoding uses a 64-character subset of US-ASCII (10 digits, 26 lowercase characters, 26 uppercase characters, '+' and '/') to represent every 6 bits of the binary or bit data by one printable character in the subset. These characters are selected so that they are universally representable. In addition, the '=' character represents a line pad character. Using this method, the size of the encoded data is 33 percent larger than the original binary or bit data.

XMLBINARY USING BASE64 is the default.

XMLBINARY USING HEX

Specifies that the assumed encoding is hexadecimal characters, as defined for XML schema type xs:hexBinary encoding. The hex encoding represents each byte (8 bits) with two hexadecimal characters. Using this method, the encoded data is twice the size of the original binary or bit data.

The XMLFOREST function can be expressed using the XMLCONCAT and XMLELEMENT functions.

This function takes an optional set of namespace declarations and one or more arguments that make up the name and element content for one or more element nodes. The result is an XML sequence containing a sequence of element nodes or the null value.

The result of the function is an XML value. The result can be null; if all the element-content-expression arguments are null and the NULL ON NULL option is in effect, the result is the null value.

Example: Generate an Emp element for each employee in the sample EMP table who was hired in 1980 or later. Concatenate the values of columns FIRSTNME and LASTNAME to form the Name attribute of the Emp element. Use XMLFOREST to create two nested elements of the Emp element: The Hiredate element and the Department element. The Hiredate element value is the HIREDATE column value. The Department element value is the WORKDEPT column value.

SELECT e.EMPNO AS "ID",
 XMLSERIALIZE(
   XMLELEMENT(
    NAME "Emp",
    XMLATTRIBUTES(e.FIRSTNME || ' ' || e.LASTNAME AS "Name"),
    XMLFOREST(e.HIREDATE AS "Hiredate",e.WORKDEPT AS "Department"))
  AS CLOB(100))
 AS "Result"
 FROM EMP e
 WHERE YEAR(HIREDATE)>='1980';

The query returns results similar to these:

ID      Result    
------------------------------------------
000070  <Emp Name="EVA PULASKI">
         <Hiredate>1980-09-30</Hiredate>
         <Department>D21</Department>
        </Emp> 
000100  <Emp Name="THEODORE SPENSER">
         <Hiredate>1980-06-19</Hiredate>
         <Department>E21</Department>
        </Emp>
000270  <Emp Name="MARIA PEREZ">
         <Hiredate>1980-09-30</Hiredate>
         <Department>D21</Department>
        </Emp> 
000290  <Emp Name="JOHN PARKER">
         <Hiredate>1980-05-30</Hiredate>
         <Department>E11</Department>
        </Emp>