Like me, some people may have gotten excited about using XMLFOREST in SQLX, only to find that NULL values result in altogether MISSING elements, as opposed to simply EMPTY elements. Many times avoiding printing elements with null values is desired functionality, but in some cases the consumer of the XML might require those elements to generated anyways, with a null value (i.e. [tagname][/tagname]).

Thus, I have found it is easiest to use XMLELEMENT instead of XMLFOREST for these cases. Note: IBM offers a directive to print empty elements for null values, but until Oracle implements such a feature, your best best is probably to leverage XMLELEMENT:

1
2
3
4
5
6
7
8
9
10
11

SELECT xmlforest(a).getstringval() FROM (SELECT 1 a FROM dual);
--returns: <A>1</A>
 
SELECT xmlforest(a).getstringval() FROM (SELECT NULL a FROM dual);
--returns: null
 
SELECT xmlelement("A", a).getstringval() FROM (SELECT 1 a FROM dual);
--returns: <A>1</A>
 
SELECT xmlelement("A", a).getstringval() FROM (SELECT NULL a FROM dual);
--returns: <A></A>

Related Information:

1. Oracle SUBSTR Function - SQL Syntax Examples

   The Oracle SUBSTR SQL Function is very common, and many times misused. Below shows the SUBSTR function along with the...

2. Oracle INSTR Function - SQL Syntax Examples

   The Oracle INSTR SQL function is popular and performs materially the same operation as instr functions in many other programming...

3. Oracle TO_DATE Function - SQL Syntax Examples

   The Oracle TO_DATE SQL function is used to convert a TEXT or NTEXT representation of a date into an Oracle...

4. Oracle SUBSTR with INTSR Function - SQL Syntax Examples

   Oracle SUBSTR and INSTR SQL functions are typically used together in practice for parsing a string. Following are...

5. Oracle TO_CHAR Function - SQL Syntax Examples (Most With Dates, TO_DATE)

   This article provides common examples of TO_CHAR using dates....

6. SQL LIKE - Oracle LIKE Operator - SQL Syntax Examples

   The reserved word LIKE in Oracle SQL represents an operator that performs some basic matching on text. LIKE provides a...

7. SQL to Query Oracle, Return XML - with SQLX

   Like many days, I had a need to query data stored in Oracle. But today was different. I needed to...

8. Microsoft CRM 3.0 - Hide Links in Left Navigation - Example Code

   Here is an example of code to get stripped down contact and account forms. This code is placed in the...