Example: Use XMLTABLE to handle missing elements

XML data can contain optional elements that are not present in all of your documents

For example, employee Peter Pan does not have a salary element since it is not a required data field. It's easy to deal with that because the XMLTABLE function produces NULL values for missing elements. You can write XMLTABLE queries as if the salary element is always present.

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

This query returns the following result. Note that the salary column for Peter Pan has the NULL value since the XML document contains no salary value.

EMPID       FIRSTNAME            LASTNAME                  SALARY
----------- -------------------- ------------------------- ----------
        901 John                 Doe                       55000
        902 Peter                Pan                       -
        903 Mary                 Jones                     64000

If you want a value other than NULL to appear for a missing element, you can define a default value to use when the expected element is missing. Here, we define the salary result column to return 0 instead of NULL.

SELECT X.*
  FROM emp,
       XMLTABLE ('$d/dept/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