This discussion is archived
7 Replies Latest reply: Jul 9, 2013 1:45 AM by odie_63 RSS

How to generate empty tag using XMLForest

user624274 Newbie
Currently Being Moderated
Hi,

I am creating an XML document using XMLAgg, XMLForest and XMlElement functions.
At some point I have an XMLForest embedded in the other XMLForest
XMLForest( e.indicator as "col1", 
           e.participant_id as "col2",

  .....
                                                                                                     
  XMLForest( tableAlias.col11 as "address1",
             tableAlias.col12 as "address2",             
           ) as "items",
)           
and within the last one I have some expressions which are empty, so the XML tags are not generated, however I have to have an empty tags:
<address2></address2>
I tried to replace an expression using "NVL" or "DECODE", but the results were the same.

I also tried to replace an embedded XMLForest with an XMLElement :
XMLForest( e.indicator as "col1", 
           e.participant_id as "col2",

  .....
                                                                                                     
  *XMLElement("items"*
             *XMLElement("address1"),*
             *XMLElement("address2")*
             
           *) as "items",*)           
however it created 2 <items> tags :
    <items>
         <items>
            <address1>value</address1>
            <address2></address2>
         </items>
     </items> 
I am working with Oracle 11.2.0 db.

Is there any way to generate an empty tag when using XMLForest function?

thank you in advance.

Edited by: user624274 on Apr 3, 2013 8:52 AM

Edited by: user624274 on Apr 3, 2013 8:53 AM
  • 1. Re: How to generate empty tag using XMLForest
    AlexAnd Guru
    Currently Being Moderated
    by mdrake      
    XMLForest will omit any node where the column contains null, XMLElement will generate an empty element
    Re: XMLElement Query
  • 2. Re: How to generate empty tag using XMLForest
    user624274 Newbie
    Currently Being Moderated
    the problem is when you using "extract('/*') as XML" an XML node is generated as a parent of a node defined in XMLElement :
    XMLForest(
    
     .....
    
    XMLElement("abcd",
                      XMLElement("col1")
                     ).extract('/*') as XML
    
    )
    the resulting XML looks like :
      <XML>
         <abcd>
            <col1></col1>
         </abcd>
       </XML>
    and I need to generate this part without an XML node.
  • 3. Re: How to generate empty tag using XMLForest
    odie_63 Guru
    Currently Being Moderated
    At some point I have an XMLForest embedded in the other XMLForest
    I generally avoid designing queries like this in the first place for the reasons you're facing now.
    My suggestion would be to use XMLForest only to generate leaf nodes, and XMLElement everywhere else to have a full control on the structure.
    Is there any way to generate an empty tag when using XMLForest function?
    No, you have to use XMLElement.

    For example, with the help of XMLConcat to group the two elements together :
    SQL> select xmlserialize(document
      2           xmlelement("EMP",
      3             xmlforest(
      4               e.empno
      5             , e.ename
      6             , xmlconcat(
      7                 xmlelement("MGR", e.mgr)
      8               , xmlelement("COMM", e.comm)
      9               ) as "INFO"
     10             )
     11           )
     12           indent
     13         )
     14  from scott.emp e
     15  where e.empno = 7839 ;
     
    XMLSERIALIZE(DOCUMENTXMLELEMEN
    --------------------------------------------------------------------------------
    <EMP>
      <EMPNO>7839</EMPNO>
      <ENAME>KING</ENAME>
      <INFO>
        <MGR/>
        <COMM/>
      </INFO>
    </EMP>
     
  • 4. Re: How to generate empty tag using XMLForest
    Rodolpho Newbie
    Currently Being Moderated

    It is really easy:

     

    In your example:

    XMLForest( e.indicator as "col1",            e.participant_id as "col2", 

     

    You will use:

    REPLACE(XMLForest( NVL(e.indicator, '#NULLValue#') as "col1",  NVL(e.participant_id, '#NULLValue#') as "col2"), '#NULLValue#', '')


    Of course you can use your own String to define NULL, but I believe the string '#NULLValue#' is pretty hard to happen in real data.


    Enjoy.

    Rodolpho

  • 5. Re: How to generate empty tag using XMLForest
    odie_63 Guru
    Currently Being Moderated

    You will use:

    REPLACE(XMLForest( NVL(e.indicator, '#NULLValue#') as "col1",  NVL(e.participant_id, '#NULLValue#') as "col2"), '#NULLValue#', '')

    I don't think so, very bad suggestion.

     

    There's an implicit conversion from XMLType to VARCHAR2, which could lead to errors if the size exceeds the datatype limit.

    Then what do you think happens if that REPLACE is wrapped into another SQL/XML function (as it's most often the case) : you lose the XML nature of the content.

     

    There's no point in using this trick. If one would have to write that, then he'd better use XMLElement in the first place, which is designed exactly for the job.

  • 6. Re: How to generate empty tag using XMLForest
    Rodolpho Newbie
    Currently Being Moderated

    Hello Odie_63.

     

    Your argument is valid, but it does not mean the solution I provided is bad.

    If you are concerned about the size of the result, it is simple, you can use like:

     

    REPLACE(XMLForest( NVL(e.indicator, '#NULLValue#') as "col1",  NVL(e.participant_id, '#NULLValue#') as "col2").getClobVal(), '#NULLValue#', '')

     

    According to Oracle documentation, REPLACE is smart and will return a CLOB (not a varchar2) because it changes the type of return according to the type of the first parameter. So you will not have issues with size.

     

    I believe the first question was related to use the final result to record to a table or send to another system (to use as integration). If it will be used just in a select field, it does not matter if it is not there or if it is there as NULL, the select of the field will always return ''.

    Other hint, I never use xmltype to store on database, because it is not compatible with other databases or integration tools. I use CLOB because it is the natural serialization of the object and I can convert and work with it inside and outside Oracle.

    But that is up to the architect and/or developer. If the system is running only on Oracle and there is no integration, maybe xmltype will save a lot of time in conversions.


    Regards.

    Rodolpho

  • 7. Re: How to generate empty tag using XMLForest
    odie_63 Guru
    Currently Being Moderated

    Hi Rodolpho,

     

    If you are concerned about the size of the result

    Personally, I'm not because I'll never have to bother about using that construct

     

    Sorry but IMO there's no excuse in using what you suggest instead of a plain XMLElement call.

    Facts :

    - XMLForest doesn't generate tag for NULL value, that's the way it works.

    - XMLElement always generates a tag

     

    If one always needs a tag then one uses XMLElement, simple as that.

     

    Regards.

     

    Message was edited by: odie_63 - removed some comments

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points