Example: Use XMLTABLE to subset result data

Often you want to produce a result containing a subset of the possible rows based on some filtering predicate.

There are several ways to produce a subset of rows. One solution is to add a WHERE clause to the query to filter using an output column. This requires all the rows to be generated only to be immediately discarded. Another solution is to use filtering predicates in the row-generating expression of the XMLTABLE function.

Suppose you need to produce rows only for employees in building 114. You can add a corresponding condition to the XMLTABLE like this:

SELECT X.*
  FROM emp,
       XMLTABLE ('$d/dept[@bldg="114"]/employee' PASSING doc AS "d"
                 COLUMNS
                      empID     INTEGER            PATH '@id',
                      firstname VARCHAR(20)        PATH 'name/first',
                      lastname  VARCHAR(25)        PATH 'name/last',
                      salary    INTEGER DEFAULT 0  PATH 'salary') AS X

This query returns a single row for Mary Jones, who is the only employee in building 114.