XMLTABLE function for returning XQuery results as a table

The XMLTABLE SQL/XML function returns a table from the evaluation of XQuery expressions.

XQuery expressions normally return values as a sequence. However, XMLTABLE lets you execute an XQuery expression and return values as a table. The table that is returned can contain columns of any SQL data type, including the XML data type.

You can pass variables to the row XQuery expression that is specified in XMLTABLE. The result of the row XQuery expression defines the portions of an XML document that you use to define a row of the returned table. You specify column XQuery expressions in the COLUMNS clause of the XMLTABLE function to generate the column values of the resulting table. In the COLUMNS clause, you define characteristics of a column by specifying the column name, data type, and how the column value is generated. Alternatively, you can omit the COLUMNS clause and let Db2 generate a single, unnamed XML column.

You can include an XMLNAMESPACES function as the first argument of XMLTABLE, to specify the XML namespace for all XQuery expressions in the XMLTABLE function. Namespace declarations in individual XQuery expressions override the XMLNAMESPACES argument.

You can specify the contents of a result table column through a column XQuery expression that you specify in the PATH clause of XMLTABLE. If you do not specify a PATH clause, Db2 uses the result table column name as the PATH argument. For example, if a result table column name is @partNum, and the input XML documents must have an attribute named partNum, the result table column values are the values of the partNum attribute.

If the column XQuery expression that defines a result table column returns an empty sequence, XMLTABLE returns a NULL value in the result table column. XMLTABLE lets you supply a default value instead of a NULL value. You do this by specifying a DEFAULT clause in the column definition.

If you want to generate a sequence number for each row that XMLTABLE generates, you can include a column definition with the FOR ORDINALITY clause. The FOR ORDINALITY clause causes XMLTABLE to generate a column with values that start at 1. If a single document generates more than one row, the sequence number is incremented by 1. For example, if an XML document generates three result table rows, the sequence numbers for those rows are 1, 2, and 3.