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