Example: Construct an XML document with values from table rows that contain null elements

This example shows how you can construct XML values suitable for publishing from table rows that contain null elements with SQL/XML publishing functions.

When an XML value is constructed using XMLELEMENT or XMLFOREST, it is possible that a null value is encountered when determining the element's content. The EMPTY ON NULL and NULL ON NULL options of XMLELEMENT and XMLFOREST allow you to specify whether an empty element or no element is generated when an element's content is null. The default null handling for XMLELEMENT is EMPTY ON NULL. The default null handling for XMLFOREST is NULL ON NULL.

This example assumes that the LOCATION column of the INVENTORY table contains a null value in one row. The following query therefore does not return the <loc> element, because XMLFOREST treats nulls as null by default:

SELECT XMLELEMENT (NAME "newElem",
                   XMLATTRIBUTES (PID AS "prodID"),
                   XMLFOREST (QUANTITY AS "quantity",
                              LOCATION AS "loc"))
FROM INVENTORY

In the result value, there is no <loc> element for the row that contains the null value.

<newElem prodID="100-100-01">
  <quantity>5<⁄quantity>
<⁄newElem>

The same query, with the EMPTY ON NULL option specified, returns an empty <loc> element:

SELECT XMLELEMENT (NAME "newElem",
                   XMLATTRIBUTES (PID AS "prodID"),
                   XMLFOREST (QUANTITY AS "quantity",
                              LOCATION AS "loc" OPTION EMPTY ON NULL))
FROM INVENTORY

In the result value, there is an empty <loc> element.

<newElem prodID="100-100-01">
  <quantity>5<⁄quantity>
  <loc⁄>
<⁄newElem>