
XMLTABLE function overview
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.
XMLTABLE advantages
- 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)
- 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.