XMLFOREST

The XMLFOREST function returns an XML value that is a sequence of XML element nodes.

>>-XMLFOREST--(--+--------------------------+------------------->
                 '-xmlnamespace-function--,-'   

   .-,------------------------------------------------.   
   V                                                  |   
>----element-content-expression--+------------------+-+--------->
                                 '-AS--element-name-'     

>--+----------------------------------------------------------+-->
   |             .--------------------------------------.     |   
   |        (1)  V   .-EMPTY ON NULL-.                  | (2) |   
   '-OPTION--------+-+-NULL ON NULL--+----------------+-+-----'   
                   |              .-USING-.           |           
                   | .-XMLBINARY--+-------+--BASE64-. |           
                   | |            .-USING-.         | |           
                   '-+-XMLBINARY--+-------+--HEX----+-'           

>--)-----------------------------------------------------------><

Notes:

The OPTION clause can only be specified if at least one xmlattributes-function or element-content-expression is specified.
The same clause must not be specified more than one time.

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 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 65-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. 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. Use employee name as its attribute and two subelements generated from columns HIRE and DEPT by using XMLFOREST as its content. The element names for the two subelements are "HIRE" and "department".

   SELECT e.id, XMLSERIALIZE ( XMLELEMENT
         ( NAME "Emp",
           XMLATTRIBUTES ( e.fname || ' '  || e.lname 
                           AS "name" ),
           XMLFOREST ( e.hire,  
                      e.dept AS "department" ) 
                    ) ) AS "result"
   FROM employees e;

The result of the query would be similar to the following result:

 ID     result
 ---------------------------------------------

 1001   <Emp name="John Smith">
          <HIRE>2000-05-24</HIRE>
          <department>Accounting</department>
            </Emp>
 1001   <Emp name="Mary Martin">
          <HIRE>1996-02-01</HIRE>
          <department>Shipping</department>
            </Emp>