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

>>-XMLFOREST--(--+------------------------------+--------------->
'-xmlnamespaces-declaration--,-'
.-,------------------------------------------------.
V |
>----element-content-expression--+------------------+-+--------->
'-AS--element-name-'
>--+------------------------------------------------------+--)-><
| .------------------------------------------. |
| V (1) .-NULL ON NULL--. | |
'-OPTION--------+-+-EMPTY ON NULL-+----------------+-+-'
| .-USING-. |
| .-XMLBINARY--+-------+--BASE64-. |
| | .-USING-. | |
'-+-XMLBINARY--+-------+--HEX----+-'
Notes:
- The same clause must not be specified more than once.
- xmlnamespace-declaration
- Specifies the XML namespace declarations that are the result of
the XMLNAMESPACES declaration. 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-declaration is not specified, namespace
declarations are not associated with the constructed XML elements.
- element-content-expression
- Specifies an expression that returns a value that is used for
the content of a generated XML element. The data type of the expression
must not be ROWID or DATALINK. 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 qualified name, or QName. See
the W3C XML namespace specifications for more details on valid names.
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 XMLELEMENT or XMLFOREST
functions that appear in element-content-expression.
- NULL ON NULL or EMPTY ON NULL
- Specifies if a null value or an empty element is returned if the
value of every element-content-expression is the null value.
This option only affects null handling of the element-content-expression arguments.
The default is NULL ON NULL.
- NULL ON NULL
- If the value of each element-content-expression is null,
a null value is returned.
- EMPTY ON NULL
- If the value of each element-content-expression is null,
an empty element 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. The default is XMLBINARY USING
BASE64.
- 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 six bits
of the binary or bit data with 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 HEX
- Specifies that the assumed encoding is hexadecimal characters,
as defined for XML schema type xs:hexBinary encoding. The hexadecimal
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 result of the function is an XML value. If the result
of any element-content-expression can be null, the result can
be null; if the result of every element-content-expression is
null and the NULL ON NULL option is in effect, the result is the null
value.
The XMLFOREST function can be expressed by using XMLCONCAT
and XMLELEMENT. For example, the following two expressions are semantically
equivalent.
XMLFOREST(xmlnamepaces-declaration, arg1 AS name1, arg2 AS name2, ...)
XMLCONCAT(XMLELEMENT(NAME name1, xmlnamespaces-declaration, arg1),
XMLELEMENT(NAME name2, xmlnamespaces-declaration, arg1),
... )
When constructing elements that will be copied
as content of another element that defines default namespaces, default
namespaces should be explicitly undeclared in the copied element to
avoid possible errors that could result from inheriting the default
namespace from the new parent element. Predefined namespace prefixes
('xs', 'xsi', 'xml', 'sqlxml') must also be declared explicitly when
they are used.
Example
Note: XMLFOREST does not insert blank
spaces or new line characters in the output. All example output has
been formatted to enhance readability.
- Construct a forest of elements with a default namespace.
SELECT EMPNO,
XMLFOREST(XMLNAMESPACES(DEFAULT 'http://hr.org',
'http://fed.gov' AS "d"),
LASTNAME, JOB AS "d:job") AS "Result"
FROM EMPLOYEE WHERE EDLEVEL = 12
This
query produces the following result:
EMPNO Result
000290 <LASTNAME xmlns:"http://hr.org" xmlns:d="http://fed.gov">PARKER
</LASTNAME>
<d:job xmlns:"http://hr.org" xmlns:d="http://fed.gov">OPERATOR</d:job>
000310 <LASTNAME xmlns:"http://hr.org" xmlns:d="http://fed.gov">SETRIGHT
</LASTNAME>
<d:job xmlns:"http://hr.org" xmlns:d="http://fed.gov">OPERATOR</d:job>
200310 <LASTNAME xmlns:"http://hr.org" xmlns:d="http://fed.gov">SPRINGER
</LASTNAME>
<d:job xmlns:"http://hr.org" xmlns:d="http://fed.gov">OPERATOR</d:job>