DB2 10.5 for Linux, UNIX, and Windows

XMLTABLE function overview

The XMLTABLE SQL table function returns a table from the evaluation of XQuery expressions. XQuery expressions normally return values as a sequence, however, use the XMLTABLE function to execute XQuery expressions and return values as a table instead. The table that is returned can contain columns of any SQL data type, including XML.

Like the XMLQUERY function, you can pass variables to the XQuery expression specified in XMLTABLE. The result of the XQuery expression is used to generate the column values of the resulting table. The structure of the resulting table is defined by the COLUMNS clause of XMLTABLE. In this clause, you define characteristics of the column by specifying the column name, data type, and how the column value is generated. A simpler syntax for passing the column name without having to specify the name explicitly is also available. See Simple column name passing with XMLEXISTS, XMLQUERY, or XMLTABLE.

The column value of the resulting table can be generated by specifying an XQuery expression in the PATH clause of XMLTABLE. If an XQuery expression is not specified for the PATH clause, the column name is used as the XQuery expression to generate the column value, and the result of the XQuery expression specified earlier in XMLTABLE becomes the external context item when generating the column value. An optional default clause can also be specified to provide a default value for the column, for the case when the XQuery expression of the PATH clause that generates the column value returns an empty sequence.

For example, the following SELECT statement references the INFO column of the CUSTOMER table in the XQuery expression in the XMLTABLE function.

SELECT X.* 
FROM CUSTOMER C, XMLTABLE ('$INFO/customerinfo' 
               COLUMNS 
               CUSTNAME CHAR(30) PATH 'name',
               PHONENUM XML PATH 'phone') 
     as X 
WHERE C.CID < 1003

If the column type in the resulting table is not XML, and the result of the XQuery expression that defines the value of the column is not an empty sequence, XMLCAST is implicitly used to convert the XML value to a value of the target data type.

The XMLTABLE function allows you to optionally declare namespaces. If you specify namespaces with the XMLNAMESPACES declaration, then these namespace bindings apply to all XQuery expressions in the XMLTABLE function call. If you declare namespace bindings without using the XMLNAMESPACES declaration, then the bindings apply only to the row XQuery expression, which follows the namespace declaration.

XMLTABLE advantages

Returning a table instead of a sequence enables the following operations to be performed from within an SQL query context:

iterate over results of an XQuery expression from within an SQL fullselect
For example, in the following query, the SQL fullselect iterates over the table that results from executing the XQuery expression "db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo" in XMLTABLE.
```
SELECT X.*
FROM XMLTABLE ('db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo'
               COLUMNS "CUSTNAME" CHAR(30) PATH 'name',
                       "PHONENUM" XML PATH 'phone')
     as X
```
insert values from stored XML documents into tables (refer to the XMLTABLE example on inserting values)

sort on values from an XML doc

For example, in the following query, results are sorted by the customer names that are stored in XML documents in the INFO column of the CUSTOMER table.

SELECT X.*
FROM XMLTABLE ('db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo'
               COLUMNS "CUSTNAME" CHAR(30) PATH 'name',
                       "PHONENUM" XML PATH 'phone')
     as X
ORDER BY X.CUSTNAME

store some XML values as relational and some as XML (refer to the XMLTABLE example on inserting values)

Important: If the XQuery expression specified in the PATH option of XMLTABLE returns:

a sequence of more than one item, then the data type of the column must be XML. If you are inserting values returned from XMLTABLE into XML columns, ensure that the values inserted are well-formed XML documents. Refer to the XMLTABLE example on inserting values for an example of handling sequences that return more than one item.
an empty sequence, then a NULL value is returned for that column value.