XMLFOREST vs XMLELEMENT - Missing vs Empty in SQLX
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> |
My PC may not be able t handle FlashBuilder...