XML values in SQL

In terms of the XQuery/XPath 2.0 data model, DB2® for i SQL defines an XML value as a sequence that contains a single document node, with a sub-tree containing the document's content. Representing an XML value as a document node guarantees that the value can be serialized to a character representation that exactly represents the XML value. This definition is referred to as XML(CONTENT) in the 2008 Database Language SQL Part 14 - XML-Related Specifications (ISO 9075-14).

In order to ensure that the XML value is of type XML(CONTENT), a document node is constructed when an XML value is created or copied within SQL.

The following process is observed during the construction of the document node:

If the content sequence contains a document node, the document node is replaced by its children.
Any atomic values in the content sequence are converted to strings and stored in text nodes, which become children of the constructed document.
Adjacent text nodes in the content sequence are merged into a single text node.
If the content sequence contains an attribute node, an error is raised.

No validation is performed on the document node. The XML 1.0 rules that govern the structure of an XML document (for example, the document node must have exactly one child that is an element node) are not enforced during the construction of the XML value.

For example:

XMLCONCAT(XMLTEXT('text node one '), XMLTEXT('text node two'))

The result XML value will be represented as a sequence of a single document node , with a single child text node 'text node one text node two'. The representation is consistent with a serialized XML document.

Other implementations of the specification (including DB2 for z/OS® and DB2 for LUW) may define the SQL XML type as an XQuery/XPath 2.0 sequence that can contain any number of items of any type of node or atomic value, This definition does not guarantee that the value can be serialized to a character representation that exactly represents the sequence. This type is referred to as XML(SEQUENCE) in the specification and is a superset of XML(CONTENT).

SQL implementations that create a value of type XML(SEQUENCE) will represent the previous XMLCONCAT expression's result as a sequence of two adjacent text nodes (with no parent document node): ('text node one', 'text node two') .

In most cases, this difference in representation is not significant. When a value of XML(SEQUENCE) is serialized, it must first be converted to XML(CONTENT), using the same process that occurs when an XML(CONTENT) value is created. Therefore, serializing an XML value produces the same result regardless of the XML type used by the implementation.

In addition, well formed XML documents that are obtained from a column in a table, host variable, parameter marker, or by using the XMLPARSE built in function contain a root document node. This causes the XML value to already be the more specific type of XML(CONTENT), even in environments that implement the more general type of XML(SEQUENCE).

Different results can occur in a small number of cases when an XML value is constructed in SQL and evaluated by an XPath expression.

For example:

select XMLSERIALIZE(OUTPUT_COL AS VARCHAR(100)) from
 XMLTABLE('$d_or_e/root/child'
           passing XMLELEMENT(NAME "root", 
                              XMLELEMENT(NAME "child", 
                                         XMLTEXT('hello world'))) as "d_or_e"
          ) X(OUTPUT_COL);

DB2 for i will assign $d_or_e to a document node that represents the XML value, The step expression is evaluated and the expected output <child>hello world</child> is returned. "root" is a child element of the document node.

However, DB2 for LUW and DB2 for z/OS will assign $d_or_e to an element "root". Since element "root" has no child called "root", this query will not return any rows.

The correct way to provide a fully platform independent solution is to write such a query so that a document node is explicitly constructed, which forces the representation to be equivalent on all platforms that comply with the specifications.

select XMLSERIALIZE(OUTPUT_COL AS VARCHAR(100)) from
 XMLTABLE('$d_or_e/root/child' passing 
                    XMLDOCUMENT(
                                XMLELEMENT(NAME "root", 
                                           XMLELEMENT(NAME "child",  
                                                      XMLTEXT('hello world')))
                                ) as "d_or_e"
 ) X(OUTPUT_COL);

Explicit construction of a document node is not necessary for XML values that are guaranteed to be XML(CONTENT), such as when:

the value is obtained from the application via host variable or parameter marker,
the value is from a column in a DB2 table, or
the XML value is explicitly or implicitly created using the XMLPARSE built in function.