Retrieving DB2 data with XQuery functions

In XQuery, a query can call one of the following functions to obtain input XML data from a DB2® database: db2-fn:sqlquery and db2-fn:xmlcolumn.

The function db2-fn:xmlcolumn retrieves an entire XML column, whereas db2-fn:sqlquery retrieves XML values that are based on an SQL fullselect.

db2-fn:xmlcolumn

The db2-fn:xmlcolumn function takes a string literal argument that identifies an XML column in a table or a view and returns a sequence of XML values that are in that column. The argument of this function is case sensitive. The string literal argument must be a qualified column name of type XML. This function allows you to extract a whole column of XML data without applying a search condition.

In the following example, the query uses the db2-fn:xmlcolumn function to get all of the purchase orders in the PURCHASE_ORDER column of the BUSINESS.ORDERS table. The query then operates on this input data to extract the cities from the shipping address in these purchase orders. The result of the query is a list of all cities to which orders are shipped:

db2-fn:xmlcolumn('BUSINESS.ORDERS.PURCHASE_ORDER')/shipping_address/city

db2-fn:sqlquery

The db2-fn:sqlquery function takes a string argument that represents a fullselect and returns an XML sequence that is a concatenation of the XML values that are returned by the fullselect. The fullselect must specify a single-column result set, and the column must have a data type of XML. Specifying a fullselect allows you to use the power of SQL to present XML data to XQuery. The function supports using parameters to pass values to the SQL statement.

In the following example, a table called BUSINESS.ORDERS contains an XML column called PURCHASE_ORDER. The query in the example uses the db2-fn:sqlquery function to call SQL to get all of the purchase orders where the ship date is June 15, 2005. The query then operates on this input data to extract the cities from the shipping addresses in these purchase orders. The result of the query is a list of all of the cities to which orders are shipped on June 15:

db2-fn:sqlquery("
SELECT purchase_order FROM business.orders
WHERE ship_date = '2005-06-15' ")/shipping_address/city

SQL and XQuery have different conventions for case-sensitivity of names. You should be aware of these differences when using the db2-fn:sqlquery and db2-fn:xmlcolumn functions.

SQL is not a case-sensitive language

By default, all ordinary identifiers, which are used in SQL statements, are automatically converted to uppercase. Therefore, the names of SQL tables and columns are customarily uppercase names, such as BUSINESS.ORDERS and PURCHASE_ORDER in the previous examples. In an SQL statement, these columns can be referenced by using lowercase names, such as business.orders and purchase_order, which are automatically converted to uppercase during processing of the SQL statement. (You can also create a case-sensitive name that is called a delimited identifier in SQL by enclosing the name in double quotation marks.)

XQuery is a case-sensitive language

XQuery does not convert lowercase names to uppercase. This difference can lead to some confusion when XQuery and SQL are used together. The string that is passed to db2-fn:sqlquery is interpreted as an SQL query and is parsed by the SQL parser, which converts all names to uppercase. Thus, in the db2-fn:sqlquery example, the table name business.orders and the column names purchase_order and ship_date can appear in either uppercase or lowercase. The operand of db2-fn:xmlcolumn, however, is not an SQL query. The operand is a case-sensitive XQuery string literal that represents the name of a column. Because the actual name of the column is BUSINESS.ORDERS.PURCHASE_ORDER, this name must be specified in uppercase in the operand of db2-fn:xmlcolumn.