Simple column name passing with XMLEXISTS, XMLQUERY, or XMLTABLE

To simplify using the XMLEXISTS predicate, the XMLQUERY scalar function, or the XMLTABLE table function, you can use a column name as a parameter in the XQuery expression specified by XMLEXISTS, XMLQUERY, or XMLTABLE without specifying the name in the passing clause.

You must use the passing clause that passes the column name as a parameter, if the parameter name being used is different from the column name being passed.

If a variable is specified explicitly in the passing clause and if the name conflicts with any variable referenced by the XQuery expression, precedence will be given to the variable in the passing clause. Assuming in the CUSTOMER table contains two XML columns named INFO and CUST, the following example retrieves XML data from INFO column:

SELECT XMLQuery('$CUST/customerinfo/name' PASSING INFO as "CUST") FROM customer

The variable CUST specified in the passing clause will be used to substitute the column INFO in the XQuery expression. The column CUST from the CUSTOMER table will not be used.

Examples: XMLQUERY and XMLEXISTS

Note that column names are case-sensitive in these examples, since they are enclosed by double quotes. When not surrounded by double quotes, regular column name rules apply: the name of the column is case-insensitive and stored in upper case.

The following example shows several SELECT statements that return the same sequence of documents from the PURCHASEORDER table. The XML documents are in column PORDER. The first two SELECT statements use the passing clause to pass the column name PORDER to the XQuery expression within the XMLQUERY scalar function. The third SELECT uses the PORDER column name as an implicitly passed parameter:

SELECT XMLQuery('$PORDER/PurchaseOrder/item/name'  PASSING porder AS "PORDER") 
   FROM purchaseorder 
SELECT XMLQuery('$porder/PurchaseOrder/item/name' PASSING porder AS "porder") 
   FROM purchaseorder
SELECT XMLQuery('$PORDER/PurchaseOrder/item/name') FROM purchaseorder

The following two examples shows several function calls that use both XMLQUERY and XMLEXISTS. Both examples return the same sequence of documents from the CUSTOMER table.

The following example uses the passing clause to explicitly specify the INFO column name as a parameter in the XMLQUERY scalar function and the XMLEXISTS predicate:

SELECT XMLQUERY ('$INFO/customerinfo/addr' passing Customer.INFO as "INFO")
FROM Customer
WHERE XMLEXISTS('$INFO//addr[city=$cityName]'
                 passing Customer.INFO as "INFO",
                 'Aurora' AS "cityName")

In the following example, the XMLQUERY function does not use a passing clause and XMLEXISTS passing clause does not specify the INFO column. The column name INFO is passed implicitly to XQuery expression in both the XMLQUERY scalar function and the XMLEXISTS predicate:

SELECT XMLQUERY ('$INFO/customerinfo/addr')
FROM Customer
WHERE XMLEXISTS('$INFO//addr[city=$cityName]'
                 passing 'Aurora' AS "cityName")

Examples: XMLTABLE

The following two examples show two INSERT statements that use the XMLTABLE table function. Both examples insert the same data into the table CUSTOMER from the table T1. The table T1 contains an XML column named CUSTLIST. The XMLTABLE function retrieves the data from the column T1.CUSTLIST and returns a table with three columns, Cid, Info, and History. The INSERT statement inserts data from the XMLTABLE function into three columns of the table CUSTOMER.

The following example uses the passing clause to explicitly specify the CUSTLIST column name as a parameter in the XMLTABLE table function:

INSERT INTO customer SELECT X.* FROM T1, 
    XMLTABLE( '$custlist/customers/customerinfo' passing T1.custlist as "custlist" 
    COLUMNS 
    "Cid" BIGINT PATH '@Cid', 
    "Info" XML PATH 'document{.}', 
    "History" XML PATH 'NULL') as X

In the following example, the XMLTABLE table function does not use a passing clause. XMLTABLE uses the column name CUSTLIST from the table T1 as an implicitly passed parameter:

INSERT INTO customer SELECT X.* FROM T1, 
    XMLTABLE( '$CUSTLIST/customers/customerinfo' 
    COLUMNS 
    "Cid" BIGINT PATH '@Cid', 
    "Info" XML PATH 'document{.}', 
    "History" XML PATH 'NULL') as X