Empty sequences returned by XMLQUERY

The XMLQUERY functions returns an empty sequence, if the XQuery expression specified within it returns an empty sequence.

For example, in the following query, XMLQUERY will return an empty sequence for each row of the CUSTOMER table that does not have a <city> element with a value of "Aurora", in the INFO column.

SELECT Cid, XMLQUERY ('$d//addr[city="Aurora"]' passing INFO as "d") AS ADDRESS
FROM CUSTOMER

Assume that there are three rows of the CUSTOMER table, but only one XML document that contains a <city> element with the value of "Aurora". The following table would result from the previous SELECT statement (the output has been formatted for clarity).

Table 1. Result table
CID	ADDRESS
1001
1002
1003	<addr country="Canada"><street>1596 Baseline</street><city>Aurora</city><prov-state>Ontario</prov-state><pcode-zip>N8X-7F8</pcode-zip></addr>

Notice how empty sequences of zero-length serialized XML, rather than NULL values, are returned for rows that do not have a <city> element with the value of "Aurora". The <addr> element is returned in the third row, however, because it satisfies the XQuery expression. In the third row, a non-empty sequence is returned.

You can avoid returning rows that contain empty sequences by applying a predicate, such as XMLEXISTS, in the WHERE clause of your statement, rather than in the SELECT clause. For example, the previous query can be rewritten as follows, moving the filtering predicate from the XMLQUERY function, to the WHERE clause:

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

The table that results from this query is as follows:

Table 2. Result table
CID	ADDRESS
1003	<addr country="Canada"><street>1596 Baseline</street><city>Aurora</city><prov-state>Ontario</prov-state><pcode-zip>N8X-7F8</pcode-zip></addr>

XMLQUERY is commonly used in a SELECT clause to return fragments of selected documents. Predicates specified in the XQuery expression of XMLQUERY do not filter rows from the result set, they are used only to determine what fragments are returned. To actually eliminate rows from your result set, you need to apply a predicate in the WHERE clause. The XMLEXISTS predicate can be used to apply predicates that depend on values within stored XML documents.