XMLTABLE is an SQL table function that returns a table from the evaluation of XQuery expressions. XQuery expressions normally return values as a sequence, however, XMLTABLE allows you to execute an XQuery expression 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.
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.
SELECT X.*
FROM XMLTABLE ('db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo'
COLUMNS "CUSTNAME" CHAR(30) PATH 'name',
"PHONENUM" XML PATH 'phone')
as X
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