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> |