XMLELEMENT

The schema is SYSIBM.

NAME element-name

Specifies the name of an XML element. element-name is an SQL identifier that must be in the form of an XML qualified name, or QName. If the name is qualified, the namespace prefix must be declared within the scope.

xmlnamespaces-declaration

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

If xmlnamespaces-declaration is not specified, namespace declarations are not associated with the constructed XML element node.

xmlattributes-function

Specifies the attributes for the XML element. The attributes are the result of the XMLATTRIBUTES function. See XMLATTRIBUTES for more information on constructing attributes.

If xmlattributes-function is not specified, attributes are not explicitly part of the constructed XML element node.

element-content-expression

The content of the generated XML element node is specified by an expression or a list of expressions. Each element-content-expression must return a value of any built-in data type or distinct type. The expression is used to construct the namespace declarations, attributes, and content of the constructed element node.

If element-content-expression is not specified, an empty string is used as the content for the element and NULL ON NULL or EMPTY ON NULL must not be specified.

OPTION

Specifies additional options for constructing the XML element. This clause has no impact on nested invocations of the XMLELEMENT function invocations that are specified 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. This option only affects null handling of element contents, not attribute values. The option is not inherited by a nested invocation of XMLELEMENT function within an element-content-expression.

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. 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.

This function takes an element name, an optional collection of namespace declarations, an optional collection of attributes, and zero or more optional arguments that make up the content of the XML element. The result is an XML sequence that contains an XML element node or the null value. If the results of all element-content-expression arguments are empty strings, the result is an XML sequence that contains an empty element.

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

Constructing an element node: The resulting element node is constructed as follows:

1. xmlnamespace-declaration adds a set of in-scope namespaces for the constructed element. Each in-scope namespace associates a namespace prefix (or the default namespace) with a namespace URI. The in-scope namespaces define the set of namespace prefixes that are available for interpreting QNames within the scope of the element.
2. If the xmlattributes-function is specified, it is evaluated and the result is a sequence of attribute nodes.
3. Each element-content-expression is evaluated and the result is converted into a sequence of nodes as follows:
   • If the result type is not XML, it is converted to an XML text node that contains the result of the element-content-expression this is mapped to XML.
   • If the result type is XML, the result is a sequence of items. Some of the items in that sequence might be document nodes. Each document node in the sequence is replaced by the sequence of its top-level children. Then for each node in the resulting sequence, a new deep copy of the node is constructed, including its children and attributes. Each copied node has a new node identity. Copied element nodes are given the type annotation xdt:untyped, and copied attribute nodes are given the type annotation xdt:untypedAtomic. For each adjacent sequence of one or more atomic values that are returned in the sequence, a new text node is constructed that contains the result of casting each atomic value to a string, with a single blank character inserted between adjacent values. If any of these atomic values cannot be cast into a string, an error is returned.
4. The result sequence of xmlattributes-function and the resulting sequences of all element-content-expression clauses are concatenated into one sequence which is called the content sequence. Any sequence of adjacent text nodes in the content sequence is merged into a single text node by concatenating their contents, with no intervening blanks. After concatenation, any text node that is a zero-length string is deleted from the content sequence.
5. If the content sequence contains an attribute node that follows a node that is not an attribute node, an error is returned. Attribute nodes that occur in the content sequence become attributes of the new element node. If two or more of these attribute nodes have the same name, an error is returned. A namespace declaration is created that corresponds to any namespace that is used in the names of the attribute nodes if the namespace URI is not in the in-scope namespaces of the constructed element.
6. Element, text, comment, and processing instruction nodes in the content sequence become the children of the constructed element node.
7. The constructed element node is given a type annotation of xdt:untyped, and each of its attributes is given a type annotation of xdt:untypedAtomic. The node name of the constructed element node is the XML element name that is specified after the NAME keyword.

Rules for using namespaces within XMLELEMENT: The following rules describe scoping of namespaces:

• The namespaces that are declared in the XMLNAMESPACES function are the in-scope namespaces of the element node that are constructed by the XMLELEMENT function. If the element node is serialized, each of its in-scope namespaces will be serialized as a namespace attribute unless it is an in-scope namespace of the parent of the element node and the parent element is also serialized.
• The scope of these namespaces is the lexical scope of the XMLELEMENT function, including the element name, the attribute names that are specified in the XMLATTRIBUTES function, and all element-content-expressions. These are used to resolve the QNames in the scope.
• If an XMLQUERY or XMLEXISTS function is in an element-content-expression, the namespaces become the statically known namespaces of the XQuery expression of the XMLQUERY or XMLEXISTS function. Statically known namespaces are used to resolve the QNames that are in the XQuery expression. If the XQuery prolog declares a namespace that has the same prefix within the scope of the XQuery expression, the namespace that is declared in the prolog will override the namespaces that are declared in the XMLNAMESPACES function.
• If an attribute of the constructed element comes from element-content-expression, its namespace might not already be declared as an in-scope namespace of the constructed element. In this case, a new namespace is created for it. If the prefix of the attribute name is already bound to a different URI by a in-scope namespace, DB2® generates a different prefix to be used in the attribute name. A namespace is created for this generated prefix. The name of the generated prefix follows the following pattern: db2ns-xx, where xx is a pair of characters chosen from the set [A-Z,a-z,0-9].

   SELECT e.empno, e.firstnme, e.lastname,
          XMLELEMENT ( NAME "foo:Emp",
            XMLNAMESPACES('http://www.foo.com' AS "foo"),
            XMLATTRIBUTES(e.empno as "serial"),
                          e.firstnme,
                          e.lastname
                          OPTION NULL ON NULL ) AS "Result"
       FROM EMP e
       WHERE e.edlevel = 12;

 EMPNO  FIRSTNME  LASTNAME  Result
-----  --------  --------  ------------------------------------

A0001  John      Parker    <foo:Emp xmlns:foo="http://www.foo.com" 
                                serial="A0001">JohnParker</foo:Emp>
B0001  (null)    Smith     <foo:Emp xmlns:foo="http://www.foo.com" 
                                serial="B0001">Smith</foo:Emp>
B0002  (null)    (null)    (null)
(null) (null)    (null)    (null)

   SELECT e.empno, e.firstnme, e.lastname,
     XMLELEMENT (NAME "foo:Emp",
                 XMLNAMESPACES('http://www.foo.com' AS "foo"),
                 XMLATTRIBUTES(e.empno as "serial"),
                               e.firstnme,
                               e.lastname
                            OPTION EMPTY ON NULL) AS "Result"
    FROM EMP e
    WHERE e.edlevel = 12;

 EMPNO    FIRSTNME    LASTNAME    Result
 -----    --------    --------    -------------------------------------

 A0001    John        Parker      <foo:Emp xmlns:foo="http://www.foo.com"
                                   serial="A0001">JohnParker</foo:Emp>
 B0001    (null)      Smith       <foo:Emp xmlns:foo="http://www.foo.com"
                                   serial="B0001">Smith</foo:Emp>
 B0002    (null)      (null)      <foo:Emp xmlns:foo="http://www.foo.com"
                                   serial="B0002"/>
 (null)   (null)      (null)      <foo:Emp xmlns:foo="http://www.foo.com"/>