DB2 Version 9.7 for Linux, UNIX, and Windows

XMLELEMENT scalar function


>>-XMLELEMENT--(--NAME--element-name---------------------------->

>--+------------------------------+----------------------------->
   '-,--xmlnamespaces-declaration-'   

>--+---------------------------+-------------------------------->
   '-,--xmlattributes-function-'   

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

>--+----------------------------------------------------------+-->
   |             .------------------------------------------. |   
   |        (1)  V   .-EMPTY ON NULL-. (2)              (3) | |   
   '-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.
NULL ON NULL or EMPTY ON NULL can only be specified if at least one element-content-expression is specified.
The same clause must not be specified more than once.

The schema is SYSIBM. The function name cannot be specified as a qualified name.

The XMLELEMENT function returns an XML value that is an XQuery element node.

NAME element-name

Specifies the name of an XML element. The name is an SQL identifier that must be in the form of an XML qualified name, or QName (SQLSTATE 42634). 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 (SQLSTATE 42635).

xmlnamespaces-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 XMLELEMENT function. The namespaces apply to any nested XML functions within the XMLELEMENT function, regardless of whether or not they appear inside another subselect.

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

xmlattributes-function

Specifies the XML attributes for the element. The attributes are the result of the XMLATTRIBUTES function.

element-content-expression

The content of the generated XML element node is specified by an expression or a list of expressions. The data type of element-content-expression cannot be a structured type (SQLSTATE 42884). The expression can be any SQL expression.

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

OPTION

Specifies additional options for constructing the XML element. If no OPTION clause is specified, the default is EMPTY ON NULL XMLBINARY USING BASE64. This clause has no impact on nested XMLELEMENT invocations specified in element-content-expression.

EMPTY ON NULL or NULL ON NULL

Specifies whether a null value or an empty element is to be returned if the values of each element-content-expression is a null value. This option only affects null handling of element contents, not attribute values. The default is EMPTY ON NULL.

EMPTY ON NULL: If the value of each element-content-expression is null, an empty element is returned.
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, 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.

This function takes an element name, an optional collection of namespace declarations, an optional collection of attributes, and zero or more arguments that make up the content of the XML element. The result is an XML sequence containing an XML element node or the null value.

The data type of the result is XML. If any of the element-content-expression arguments can be null, the result can be null; if all the element-content-expression argument values are null and the NULL ON NULL option is in effect, the result is the null value.

Note:

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', and 'sqlxml') must also be declared explicitly when they are used.
Constructing an element node: The resulting element node is constructed as follows:
1. The xmlnamespaces-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 whose content is the result of element-content-expression mapped to XML according to the rules of mapping SQL data values to XML data values (see the table that describes supported casts from non-XML values to XML values in "Casting between data types").
  - If the result type is XML, then in general 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 and attribute nodes preserve their type annotation. For each adjacent sequence of one or more atomic values returned in the sequence, a new text node is constructed, containing the result of casting each atomic value to a string, with a single blank character inserted between adjacent values. Adjacent text nodes in the content sequence are merged into a single text node by concatenating their contents, with no intervening blanks. After concatenation, any text node whose content is a zero-length string is deleted from the content sequence.
4. The result sequence of XML attributes and the resulting sequences of all element-content-expression specifications 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. If all the element-content-expression arguments are empty strings, or an element-content-expression argument is not specified, an empty element is returned.
5. The content sequence must not contain an attribute node following a node that is not an attribute node (SQLSTATE 10507). Attribute nodes occurring in the content sequence become attributes of the new element node. Two or more of these attribute nodes must not have the same name (SQLSTATE 10503). A namespace declaration is created corresponding to any namespace 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 xs:anyType, and each of its attributes is given a type annotation of xdt:untypedAtomic. The node name of the constructed element node is element-name specified after the NAME keyword.
Rules for using namespaces within XMLELEMENT: Consider the following rules about scoping of namespaces:
- The namespaces declared in the XMLNAMESPACES declaration are the in-scope namespaces of the element node constructed by the XMLELEMENT function. If the element node is serialized, then 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 serialized too.
- If an XMLQUERY or XMLEXISTS is in an element-content-expression, then the namespaces becomes the statically known namespaces of the XQuery expression of the XMLQUERY or XMLEXISTS. Statically known namespaces are used to resolve the QNames in the XQuery expression. If the XQuery prolog declares a namespace with the same prefix, within the scope of the XQuery expression, the namespace declared in the prolog will override the namespaces declared in the XMLNAMESPACES declaration.
- If an attribute of the constructed element comes from an 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 this would result in a conflict, which means that the prefix of the attribute name is already bound to a different URI by a in-scope namespace, DB2® generates a prefix that does not cause such a conflict and the prefix used in the attribute name is changed to the new prefix, and a namespace is created for this new prefix. The generated new prefix follows the following pattern: "db2ns-xx", where "x" is a character chosen from the set [A-Z,a-z,0-9]. For example:
```
   VALUES XMLELEMENT(
     NAME "c", XMLQUERY(
       'declare namespace ipo="www.ipo.com"; $m/ipo:a/@ipo:b'
         PASSING XMLPARSE(
           DOCUMENT '<tst:a xmlns:tst="www.ipo.com" tst:b="2"/>'
         ) AS "m"
     )
   )
```
  returns:
```
<c xmlns:tst="www.ipo.com" tst:b="2"/>
```
  A second example:
```
   VALUES XMLELEMENT(
     NAME "tst:c", XMLNAMESPACES(
       'www.tst.com' AS "tst"
     ),
     XMLQUERY(
       'declare namespace ipo="www.ipo.com"; $m/ipo:a/@ipo:b'
         PASSING XMLPARSE(
           DOCUMENT '<tst:a xmlns:tst="www.ipo.com" tst:b="2"/>'
         ) AS "m"
     )
   )
```
  returns:
```
<tst:c xmlns:tst="www.tst.com" xmlns:db2ns-a1="www.ipo.com"
  db2ns-a1:b="2"/>
```

Examples:

Note: XMLELEMENT does not insert blank spaces or new line characters in the output. All example output has been formatted to enhance readability.

Construct an element with the NULL ON NULL option.

   SELECT E.FIRSTNME, E.LASTNAME, XMLELEMENT(
     NAME "Emp", XMLELEMENT(
       NAME "firstname", E.FIRSTNME
     ), 
     XMLELEMENT(
       NAME "lastname", E.LASTNAME
     )
     OPTION NULL ON NULL
   )
   AS "Result"
   FROM EMPLOYEE E
   WHERE E.EDLEVEL = 12

This query produces the following result:

FIRSTNME     LASTNAME        Emp
JOHN         PARKER          <Emp><firstname>JOHN</firstname>
                               <lastname>PARKER</lastname></Emp>
MAUDE        SETRIGHT        <Emp><firstname>MAUDE</firstname>
                               <lastname>SETRIGHT</lastname></Emp>
MICHELLE     SPRINGER        <Emp><firstname>MICHELLE</firstname>
                               <lastname>SPRINGER</lastname></Emp>

Produce an element with a list of elements nested as child elements.

   SELECT XMLELEMENT(
     NAME "Department", XMLATTRIBUTES(
       E.WORKDEPT AS "name"
     ),
     XMLAGG(
       XMLELEMENT(
         NAME "emp", E.FIRSTNME
       )
       ORDER BY E.FIRSTNME
     )
   )
   AS "dept_list"
   FROM EMPLOYEE E
   WHERE E.WORKDEPT IN ('A00', 'B01')
   GROUP BY WORKDEPT

This query produces the following result:

dept_list
<Department name="A00">
<emp>CHRISTINE</emp>
<emp>SEAN</emp>
<emp>VINCENZO</emp>
</Department>
<Department name="B01">
<emp>MICHAEL</emp>
</Department>

Creating nested XML elements specifying a default XML element namespace and using a subselect.

SELECT XMLELEMENT(
         NAME "root",
         XMLNAMESPACES(DEFAULT 'http://mytest.uri'),
         XMLATTRIBUTES(cid),
            (SELECT 
               XMLAGG(
                 XMLELEMENT( 
                   NAME "poid", poid
                 )
               )
             FROM purchaseorder
             WHERE purchaseorder.custid = customer.cid 
            )
       )
FROM customer 
WHERE cid = '1002'

The statement returns the following XML document with the default element namespace declared in the root element:

<root xmlns="http://mytest.uri" CID="1002">
  <poid>5000</poid>
  <poid>5003</poid>
  <poid>5006</poid>
</root>

Using a common table expression with XML namespaces.
When an XML element is constructed with a common table expression and the element is used in elsewhere in the same SQL statement, any namespace declarations should be specified as part of the element construction. The following statement specifies the default XML namespace in both the common table expression that uses the PURCHASEORDER table to create the poid elements and the SELECT statement that uses the CUSTOMER table to create the root element.
```
WITH tempid(id, elem) AS
  (SELECT custid, XMLELEMENT(NAME "poid", 
     XMLNAMESPACES(DEFAULT 'http://mytest.uri'), 
       poid)
   FROM purchaseorder )
SELECT XMLELEMENT(NAME "root",
         XMLNAMESPACES(DEFAULT 'http://mytest.uri'),
            XMLATTRIBUTES(cid),
            (SELECT XMLAGG(elem)
             FROM tempid
             WHERE tempid.id = customer.cid )
        )
FROM customer 
WHERE cid = '1002'
```
The statement returns the following XML document with a default element namespace declared in the root element.
```
<root xmlns="http://mytest.uri" CID="1002">
  <poid>5000</poid>
  <poid>5003</poid>
  <poid>5006</poid>
</root>
```
In the following statement, the default element namespace is declared only in the SELECT statement that uses the CUSTOMER table to create the root element:
```
WITH tempid(id, elem) AS
    (SELECT custid, XMLELEMENT(NAME "poid", poid)
     FROM purchaseorder )
SELECT XMLELEMENT(NAME "root",
         XMLNAMESPACES(DEFAULT 'http://mytest.uri'),
            XMLATTRIBUTES(cid),
            (SELECT XMLAGG(elem)
             FROM tempid
             WHERE tempid.id = customer.cid )
       )
FROM customer 
WHERE cid = '1002'
```
The statement returns the following XML document with the default element namespace declared in the root element. Because the poid elements are created in the common table expression without a default element namespace declaration, the default element namespace for the poid elements is not defined. In the XML document, the default element namespace for the poid elements is set to an empty string "" because the default element namespace for the poid elements is not defined, and the poid elements do not belong to the default element namespace of the root element xmlns="http://mytest.uri".
```
<root xmlns="http://mytest.uri" CID="1002">
  <poid xmlns="">5000</poid>
  <poid xmlns="">5003</poid>
  <poid xmlns="">5006</poid>
</root>
```