XMLFOREST vs XMLELEMENT – Missing vs Empty in SQLX

Posted on Feb 23, 2010 in Technical Tips | 0 comments



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>
Bookmark and Share

Related Information:

  1. Oracle SUBSTR Function – SQL Syntax Examples
    The Oracle SUBSTR SQL Function...
  2. Oracle INSTR Function – SQL Syntax Examples
    The Oracle INSTR SQL function...
  3. Oracle TO_DATE Function – SQL Syntax Examples
    The Oracle TO_DATE SQL function...
  4. Create Table – ORA-00907: missing right parenthesis — CHECK and DEFAULT Order Swapped
    Most people, including myself, don’t...
  5. Oracle SUBSTR with INTSR Function – SQL Syntax Examples
    Oracle SUBSTR and INSTR SQL...
  6. Oracle TO_CHAR Function – SQL Syntax Examples (Most With Dates, TO_DATE)
    This article provides common examples...
  7. SQL LIKE – Oracle LIKE Operator – SQL Syntax Examples
    The reserved word LIKE in...
  8. SQL to Query Oracle, Return XML – with SQLX
    Like many days, I had...

Leave a Reply