Empty sequences returned by XMLQUERY

XMLQUERY returns an empty sequence if the XQuery expression returns an empty sequence.

Example: In the following query, XMLQUERY returns 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 ('declare default element namespace "http://posample.org";
  //addr[city="Aurora"]' passing INFO) 
AS "ADDRESS FROM INFO"
FROM CUSTOMER

Only one XML document that contains a <city> element with the value of Aurora. The following table results from the previous SELECT statement:

Table 1. Example of a result table from XMLQUERY that returns empty sequences
CID	ADDRESS FROM INFO
1000	<?xml version="1.0" encoding="IBM037"?>
1001	<?xml version="1.0" encoding="IBM037"?>
1002	<?xml version="1.0" encoding="IBM037"?>
1003	<?xml version="1.0" encoding="IBM037"?><addr xmlns="http:⁄⁄posample.org" country="Canada"><street>1596 Baseline<⁄street><city>Aurora<⁄city><prov-state>Ontario<⁄prov-state><pcode-zip>N8X-7F8<⁄pcode-zip><⁄addr>
1004	<?xml version="1.0" encoding="IBM037"?>
1005	<?xml version="1.0" encoding="IBM037"?>

Empty sequences are returned for rows that do not have a <city> element with the value of Aurora. Empty sequences are returned as strings of length 0, rather than NULL values. The <addr> element is returned in the third row, however, because it satisfies the XQuery expression.

You can filter out the rows with empty sequences by applying a predicate, such as the XMLEXISTS predicate.

Example: Rewrite the previous query to return only rows with non-empty sequences:

SELECT Cid, XMLQUERY ('declare default element namespace "http://posample.org";
                       /customerinfo/addr' passing c.INFO)
 AS "ADDRESS FROM INFO"
FROM Customer as c
WHERE XMLEXISTS ('declare default element namespace "http://posample.org";
                  //addr[city="Aurora"]' passing c.INFO)

The table that results from this query is as follows:

Table 2. Result table
CID	ADDRESS FROM INFO
1003	<?xml version="1.0" encoding="IBM037"?><addr xmlns="http:⁄⁄posample.org" country="Canada"><street>1596 Baseline<⁄street><city>Aurora<⁄city><prov-state>Ontario<⁄prov-state><pcode-zip>N8X-7F8<⁄pcode-zip><⁄addr>