XMLQUERY, XMLEXISTS, and XQuery in DB2

How SQL talks to XML

Relational SQL sees an XML column as one value per row. It does not automatically know about nested elements. To walk inside the document you embed an XQuery expression as a character string constant in SQL/XML. Db2 evaluates that expression against XML you pass in, then hands the result back as either a yes/no (predicate), an XML sequence (XMLQUERY), or a table (XMLTABLE).

A practical pattern used in IBM samples: filter with XMLEXISTS, then project with XMLQUERY (or shred with XMLTABLE). That keeps the WHERE clause cheap and index-friendly while the SELECT list does the construction work.

1
2
3
4
5
6
7
8
9
10
11
12
SELECT CID,
       XMLQUERY(
         'declare default element namespace "http://posample.org";
          $doc/customerinfo/name'
         PASSING INFO AS "doc"
       ) AS CUST_NAME
  FROM MYCUSTOMER AS C
 WHERE XMLEXISTS(
         'declare default element namespace "http://posample.org";
          $i/customerinfo/addr[city="Toronto"]'
         PASSING C.INFO AS "i"
       );

The declare default element namespace prolog is required when the stored document uses a default namespace. If you omit it, a path like /customerinfo looks for elements in no namespace and matches nothing—a classic “query returns empty but the XML is right there” bug.

XML predicates and XMLEXISTS

An XML predicate tests XML the way LIKE tests strings. XMLEXISTS is the main one: it is true when the XQuery expression yields a non-empty sequence, false when the sequence is empty. It does not return the nodes; it only answers “did anything match?”

1
2
3
4
5
6
DELETE FROM MYCUSTOMER
 WHERE XMLEXISTS(
         'declare default element namespace "http://posample.org";
          /customerinfo/phone[@type="cell"]'
         PASSING INFO
       );

When the XQuery uses a context item (a path that starts at the document root without a variable), PASSING can supply that context XML directly. When you name a variable with AS "i", write $i/... in the expression. Names are case-sensitive in XQuery even though SQL identifiers might not be.

What “exists” means

• Match found — sequence has one or more items; predicate is true
• No match — empty sequence; predicate is false (not UNKNOWN like a null comparison)
• NULL XML column — there is no document to query; the predicate does not find nodes (treat as “no match” for filtering purposes—do not assume it behaves like IS NULL unless you also test the column)

Pair XMLEXISTS with a relational predicate when you can: WHERE STATUS = 'OPEN' AND XMLEXISTS(...). Db2 can use a relational index on STATUS first, then apply XML logic to fewer documents.

XPath

XPath is the path language: steps separated by slashes, predicates in square brackets, @ for attributes. It is the subset of XQuery you should memorize first.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Element children
'/order/item'
 
-- Predicate on a child
'/order/item[qty > 10]'
 
-- Attribute
'/order/item[@sku="W42"]'
 
-- Text value of an element
'/customerinfo/name/text()'
 
-- Abbreviated descendant
'//phone'

• / — document (or context) root, then a child step
• // — descendant-or-self; convenient but easy to over-match
• [expr] — keep nodes for which expr is true (or a non-empty node sequence)
• @name — attribute node named name
• text() — text node children
• position predicates — [1] is the first item in the sequence for that step

XPath predicates in XMLEXISTS are the forms most likely to match an XML index defined with an XMLPATTERN. Keep paths as specific as the documents allow. Wild descendant searches and functions inside predicates often block index matching.

XQuery and XQuery expressions

XQuery includes XPath and adds expressions that look more like a small programming language. In Db2 for z/OS, XQuery is not a standalone statement you type at SPUFI by itself. You place an XQuery expression inside XMLQUERY, XMLEXISTS, or XMLTABLE as a string constant. That constant must not be empty or all blanks, and it must not be an updating XQuery expression in XMLQUERY.

Besides path expressions, common XQuery expression kinds include:

• FLWOR — iteration and construction (next section)
• Constructors — element and attribute constructors that build new XML (direct constructors with tags, or computed constructors)
• Conditionals — if-then-else on sequences
• Comparisons and arithmetic — value vs general comparison; be careful with typed vs untyped values
• Castable expressions — test whether a value can be cast to an XML Schema type
• Function calls — fn: prefixed functions documented for Db2 XQuery

IBM’s performance note is blunt: XQuery FLWOR is not indexable. Even if an XML index exists for /Order/OrderLine/ID, an XMLEXISTS written as a FLWOR over that path will not use the index the way a plain XPath predicate can. Preferred practice: XPath in XMLEXISTS (and in XMLTABLE row expressions when you want index use), then XQuery in XMLQUERY or in XMLTABLE column PATH expressions to reshape only the rows that already qualified.

XQuery FLWOR

FLWOR stands for for, let, where, order by, return. Think of it as XQuery’s SELECT: for/let are FROM, where is WHERE, order by is ORDER BY, return is the SELECT list.

1
2
3
4
5
6
7
8
9
SELECT XMLQUERY(
         'declare default element namespace "http://posample.org";
          for $d in $doc/customerinfo
          where $d/addr/city = "Toronto"
          order by $d/name
          return $d/name'
         PASSING INFO AS "doc"
       )
  FROM MYCUSTOMER;

for $d in ... creates one tuple per matching node. let $x := ... binds once per tuple without iterating. Use let for computed values you would otherwise repeat. where drops tuples. order by sorts remaining tuples. return builds the output sequence—often the original node, a constructed element, or a concatenation of several pieces.

Nested FLWOR expressions are legal. Keep them in XMLQUERY after you have already restricted the row set. Evaluating a heavy FLWOR against every XML column value in a large table is a classic elapsed-time surprise.

XMLQUERY

XMLQUERY returns the XML value of evaluating the XQuery constant with the PASSING arguments. Typical options you will see in the syntax:

• xquery-expression-constant — required string; supported XQuery syntax only; not an updating expression
• PASSING — XML (and certain SQL scalars) supplied as the context item and/or as named XQuery variables
• BY REF — XML values passed by reference so node identity is preserved for comparisons that care about the same tree
• RETURNING SEQUENCE — the result is a sequence (the XMLQUERY model)
• EMPTY ON EMPTY / NULL ON EMPTY — what to do if the expression returns an empty sequence: empty XML sequence versus a null XML value

PASSING arguments must not be ROWID, TIMESTAMP, binary string, REAL, DECFLOAT, FOR BIT DATA character, or a sequence expression. Character and graphic arguments must not be LOBs. If you need a CLOB document as input, store or cast it to XML first (XMLPARSE), then PASSING the XML value.

1
2
3
4
5
-- Named variable
XMLQUERY('$d/order/comment' PASSING ORDER_DOC AS "d")
 
-- Empty vs null when nothing matches
XMLQUERY('$d/order/missing' PASSING ORDER_DOC AS "d" NULL ON EMPTY)

XMLQUERY is not indexable. Do not use it as your only filter in WHERE (and you generally cannot use it as a boolean predicate anyway—that is XMLEXISTS’s job). Select the XML fragment you need after the row has qualified.

XMLTABLE

XMLTABLE belongs in the FROM clause. It evaluates a row XQuery expression and, for each item in that sequence, builds one result row whose columns come from PATH expressions (or defaults). That is how you turn repeating XML into something you can JOIN, GROUP BY, and load into a report.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT O.ORDER_ID,
       X.SKU,
       X.QTY,
       X.PRICE
  FROM ORDER_XML O,
       XMLTABLE(
         XMLNAMESPACES(DEFAULT 'http://example.org/po'),
         '$d/order/item'
         PASSING O.ORDER_DOC AS "d"
         COLUMNS
           SKU   VARCHAR(20)  PATH '@sku',
           QTY   INTEGER      PATH 'qty',
           PRICE DECIMAL(9,2) PATH 'price'
       ) AS X;

Important pieces:

• XMLNAMESPACES — optional first argument; DEFAULT namespace applies to unprefixed names in the row and column paths
• Row expression — XPath/XQuery that produces one item per output row (here each item element)
• PASSING — the XML document and any extra variables the paths need
• COLUMNS — SQL name, SQL type, and PATH relative to the row item. PATH '.' means the row item itself (useful for an XML column of the fragment)

If the row expression is a simple XPath that matches an XML index pattern, XMLTABLE can participate in index usage. If you write a FLWOR as the row expression, you usually give that up. Column PATH expressions can be richer XQuery because they run only for rows already produced.

XMLTABLE is a join: if a document has zero matching items, that ORDER_XML row does not appear (like an inner join). If you need documents with no items, use a left outer join to the XMLTABLE reference.

XMLCAST

XMLCAST(expression AS data-type) converts between XML and SQL types. Either the operand or the target must be XML. Casting XML to XML is a no-op. You cannot XMLCAST to a distinct type. NULL AS XML yields a null XML value.

1
2
3
4
5
6
SELECT ORDER_ID,
       XMLCAST(
         XMLQUERY('$d/order/total' PASSING ORDER_DOC AS "d")
         AS DECIMAL(15,2)
       ) AS ORDER_TOTAL
  FROM ORDER_XML;

Common targets from XML atomics include VARCHAR, CHAR, integers, DECIMAL, DATE, TIME, and TIMESTAMP. When the target is XML, SQL DATE becomes xs:date, TIME becomes xs:time, TIMESTAMP WITH TIME ZONE becomes xs:dateTime (trailing zeros in fractional seconds are not kept). Default encoding for character results is Unicode; you can specify CCSID on the target type when you need a different scheme.

If XMLQUERY returns an empty sequence, XMLCAST to a non-XML type typically yields null—handle that with COALESCE if the business wants zero. If it returns more than one item, the cast is not a simple atomic conversion; extract a single value in XQuery first (for example ($d/order/total)[1]).

Putting the pieces together

A production query often layers all four tools:

1. Restrict rows with relational predicates plus XMLEXISTS XPath (indexable when designed that way).
2. Shred repeating nodes with XMLTABLE in FROM.
3. Pull leftover XML fragments with XMLQUERY if you still need markup.
4. Convert atomics with XMLCAST for arithmetic, joins, and host variables.

COBOL and other host languages receive XMLQUERY results as XML (or serialized strings if you XMLSERIALIZE). XMLTABLE columns of DECIMAL and VARCHAR map like any other SELECT list item. Do not fetch a multi-megabyte document into a PIC X(80) just to read one element—extract in SQL.

Explain It Like I'm Five

An XML document is a tree house with labeled rooms. XPath is the hallway directions: “second floor, room named item, the one with sku W42.” XMLEXISTS asks “is anyone in that room?” and only keeps houses where the answer is yes. XMLQUERY brings back the furniture from that room still packed as XML. XMLTABLE unpacks every toy in the toy chest onto a spreadsheet, one row per toy. XMLCAST puts a price tag number into your calculator so it is a real number, not a scrap of XML paper. FLWOR is walking through every room with a checklist, maybe building a new smaller tree house from what you found—fun, but slower than just asking “is the rush flag on?” at the front door.

Exercises

1. Write an XMLEXISTS predicate that is true when an XML column DOC contains an element /claim/status with text OPEN. Include a default namespace declare if the documents use http://example.org/claim.
2. Write XMLQUERY that returns the name element from $doc/customerinfo, PASSING column INFO AS "doc".
3. Explain in two sentences why a FLWOR in XMLEXISTS can be slower than the equivalent XPath predicate.
4. Design an XMLTABLE that produces SKU VARCHAR(20) and QTY INTEGER from /order/item nodes, using attribute @sku and child qty.
5. Wrap an XMLQUERY of /order/total in XMLCAST to DECIMAL(9,2). What happens if that element is missing?