7 Replies Latest reply: Jul 9, 2013 3:45 AM by odie_63 RSS

    How to generate empty tag using XMLForest

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

                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

                  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

                    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

                      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