1 2 Previous Next 20 Replies Latest reply on Mar 19, 2013 10:59 PM by mdrake-Oracle

    Remove Whitespace from Node's text

    Boneist
      Hi,

      We're in the proces of migrating our 10.2.0.4 db to 11.2.0.3, and have discovered an issue with one of our queries - basically, we extract a node's text as a number, and if that node consists of whitespace only, it's now returning a 0 in 11.2.0.3 rather than the NULL it used to return in 10.2.0.4 (and earlier versions).

      Here's the examples I've been working with:
      select extract(xmltype('<root><test> </test></root>'), '/root/test/text()').getnumberval() from dual;
      select extract(xmltype('<root><test>
      </test></root>'), '/root/test/text()').getnumberval() from dual;
      I've raised an SR with Oracle, due to the difference in behaviour from our 10g db, but in the meantime, I need to workaround this issue. Is there a neat way of removing whitespace from the node's text? I'd rather avoid having to convert the xmltype back to a clob to do a replace, or extracting the node as text and then trimming/replacing and converting it to a number, if at all possible.
        • 1. Re: Remove Whitespace from Node's text
          odie_63
          Hi,

          I can reproduce the "new" behaviour on 10.2.0.5 too :
          SQL> select * from v$version;
          
          BANNER
          ----------------------------------------------------------------
          Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
          PL/SQL Release 10.2.0.5.0 - Production
          CORE    10.2.0.5.0      Production
          TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
          NLSRTL Version 10.2.0.5.0 - Production
          
          SQL> select extract(xmltype('<root><test>
            2  </test></root>'), '/root/test/text()').getnumberval() as result
            3  from dual;
          
              RESULT
          ----------
                   0
          On 11.2.0.3, if you can invest some time in refactoring, I'd suggest you use non deprecated features instead :
          SQL> select * from v$version;
          
          BANNER
          --------------------------------------------------------------------------------
          Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
          PL/SQL Release 11.2.0.3.0 - Production
          CORE    11.2.0.3.0      Production
          TNS for Linux: Version 11.2.0.3.0 - Production
          NLSRTL Version 11.2.0.3.0 - Production
          
          SQL> select xmlcast(
            2           xmlquery('normalize-space(/root/test)'
            3             passing xmltype('<root><test>
            4   </test></root>')
            5             returning content
            6           )
            7           as number
            8         ) as result
            9  from dual ;
          
              RESULT
          ----------
          
          
          SQL> select xmlcast(
            2           xmlquery('normalize-space(/root/test)'
            3             passing xmltype('<root><test>123</test></root>')
            4             returning content
            5           )
            6           as number
            7         ) as result
            8  from dual ;
          
              RESULT
          ----------
                 123
          • 2. Re: Remove Whitespace from Node's text
            Boneist
            Thanks Odie - I could have searched for a month of Sundays and still not found that as a solution!

            I had a feeling there would be a neat solution - and it's non-deprecated too, which is a bonus! *{:-D                                                                                                                                                                                                                                                                                                                                                                                                                       
            • 3. Re: Remove Whitespace from Node's text
              Boneist
              A further question regarding this - in my code, the query containing the EXTRACT in my initial post contains lots of other EXTRACTs and EXTRACTVALUEs (the xml is passed in as a parameter, it's not read from a table).

              Whilst I could certainly take the above suggestion and incorporate it into my code, would I be better off using XMLTABLE to put the data into columns as strings, and then using to_number & trim in the outer query?

              eg.
              select x.*, to_number(trim(x.test))
              from   xmltable('/root' passing xmltype('<root><test>  </test></root>')
                              columns "TEST" VARCHAR2(20) PATH 'test') x;
              Or is there a better way whilst still using XMLTABLE?
              • 4. Re: Remove Whitespace from Node's text
                Jason_(A_Non)
                Just to add since I was curious with another non-deprecated method, I get consistent results with Odie across both 10.2.0.4 and 11.1.0.6 for the following
                Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
                 
                SQL> 
                SQL> select result
                  2    from xmltable('/root/test'
                  3                  passing XMLType('<root><test>
                  4       </test></root>')
                  5                  COLUMNS
                  6                  result  NUMBER  PATH '.');
                 
                    RESULT
                ----------
                 
                SQL> 
                SQL> select result
                  2    from xmltable('/root/test'
                  3                  passing XMLType('<root><test>123</test></root>')
                  4                  COLUMNS
                  5                  result  NUMBER  PATH '.');
                 
                    RESULT
                ----------
                       123
                But 11.1.0.6 sometimes functions more like 10.2.0.4 as shown by
                Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
                 
                SQL> 
                SQL> select extract(xmltype('<root><test>
                  2     </test></root>'), '/root/test/text()').getnumberval() as result
                  3  from dual;
                 
                    RESULT
                ----------
                 
                It would seem 11.2.0.3 doesn't like my first approach though, so I had to add in "normalize-space()"
                Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
                 
                SQL> select result
                  2    from xmltable('/root/test'
                  3                  passing XMLType('<root><test>
                  4       </test></root>')
                  5                  COLUMNS
                  6                  result  NUMBER  PATH '.');
                 
                select result
                  from xmltable('/root/test'
                                passing XMLType('<root><test>
                     </test></root>')
                                COLUMNS
                                result  NUMBER  PATH '.')
                 
                ORA-01722: invalid number
                
                SQL> select result
                  2    from xmltable('normalize-space(/root/test)'
                  3                  passing XMLType('<root><test>
                  4       </test></root>')
                  5                  COLUMNS
                  6                  result  NUMBER  PATH '.');
                 
                    RESULT
                ----------
                Going backwards, it is interesting that 10.2.0.4 chokes on normal-space, though 11.1.0.6 returned NULL for this
                Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
                
                SQL> select result
                  2    from xmltable('normalize-space(/root/test)'
                  3                  passing XMLType('<root><test>
                  4       </test></root>')
                  5                  COLUMNS
                  6                  result  NUMBER  PATH '.');
                  
                 
                select result
                  from xmltable('normalize-space(/root/test)'
                                passing XMLType('<root><test>
                     </test></root>')
                                COLUMNS
                                result  NUMBER  PATH '.')
                 
                ORA-19280: XQuery dynamic type mismatch: expected atomic value - got node
                And here I've had such love for XMLTable. I was hoping to find one non-deprecated approach that worked across all those versions.

                Edited by: A_Non on Mar 18, 2013 10:45 AM
                Just now saw your post above Boneist so use what you can from what I dug up with XMLTable and how it works across versions.
                1 person found this helpful
                • 5. Re: Remove Whitespace from Node's text
                  Boneist
                  Thanks - how do I use the normalize-space if I've got more than one node that needs to be stripped of whitespace, but some that don't?

                  eg:
                  select x.*
                  from   xmltable('/root' passing xmltype('<root><test1>  </test1>
                                                             <level1>
                                                               <test2>123 </test2>
                                                               <test3>some   string  </test3>
                                                             </level1>
                                                             <level2>
                                                               <test4>  hi</test4>
                                                               <test5>  345</test5>
                                                               <test6> 2 </test6>
                                                             </level2>
                                                           </root>')
                                  columns "XML" xmltype PATH '.') x
                  where all the testN nodes need to be extracted, and test1, -2, -5 and -6 nodes need to be numbers, whilst the rest stay as is? Is it still possible? (she says, googling furiously and going nowhere fast!)
                  • 6. Re: Remove Whitespace from Node's text
                    Jason_(A_Non)
                    Are you looking for something like?
                    SQL> select x.*
                      2  from   xmltable('/root' passing xmltype('<root><test1>  </test1>
                      3                                             <level1>
                      4                                               <test2>123 </test2>
                      5                                               <test3>some string</test3>
                      6                                             </level1>
                      7                                             <level2>
                      8                                               <test4>hi</test4>
                      9                                               <test5>  345</test5>
                     10                                               <test6> 2 </test6>
                     11                                             </level2>
                     12                                           </root>')
                     13                  columns
                     14                    "XML" xmltype PATH '.',
                     15                    test1  NUMBER  PATH 'normalize-space(test1)',
                     16                    test5  NUMBER  PATH 'normalize-space(level2/test5)') x
                     17  ;
                     
                    XML                                                                                   TEST1      TEST5
                    -------------------------------------------------------------------------------- ---------- ----------
                    <root><test1>  </test1><level1><test2>123 </test2><test3>some string</test3></le                   345
                    Are the nodes level1 and level2 really named differently or are they the same. What about the testN nodes across levels. Same names or different? Just seeing what options you have available (and that I can provide answers for).
                    1 person found this helpful
                    • 7. Re: Remove Whitespace from Node's text
                      Boneist
                      oooh, that might just be what I'm after, thanks!

                      Let me go play...

                      (the example xml is not a true representation of what I have to work with, but should be good enough for what I need to do)
                      • 8. Re: Remove Whitespace from Node's text
                        AlexAnd
                        as idea
                        you can use XMLTransform http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions229.htm
                        so i used xslt from above doc
                        SQL> select * from v$version;
                         
                        BANNER
                        ----------------------------------------------------------------
                        Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
                        PL/SQL Release 10.2.0.1.0 - Production
                        CORE     10.2.0.1.0     Production
                        TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
                        NLSRTL Version 10.2.0.1.0 - Production
                         
                        SQL> 
                        SQL> select x.*
                          2      from   xmltable('/root' passing
                          3      xmltransform(
                          4      xmltype('<root><test1>  </test1>
                          5                                                 <level1>
                          6                                                   <test2>123 </test2>
                          7                                                   <test3>some string</test3>
                          8                                                 </level1>
                          9                                                 <level2>
                         10                                                   <test4>hi</test4>
                         11                                                   <test5>  345</test5>
                         12                                                  <test6> 2 </test6>
                         13                                                </level2>
                         14                                              </root>')
                         15                   ,
                         16                    xmltype('<?xml version="1.0"?>
                         17      <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
                         18        <xsl:output encoding="utf-8"/>
                         19        <!-- alphabetizes an xml tree -->
                         20        <xsl:template match="*">
                         21          <xsl:copy>
                         22            <xsl:apply-templates select="*|text()">
                         23              <xsl:sort select="name(.)" data-type="text" order="ascending"/>
                         24            </xsl:apply-templates>
                         25          </xsl:copy>
                         26        </xsl:template>
                         27        <xsl:template match="text()">
                         28          <xsl:value-of select="normalize-space(.)"/>
                         29        </xsl:template>
                         30      </xsl:stylesheet> ')
                         31                   )
                         32                     columns
                         33                       "XML" xmltype PATH '.',
                         34                       test1  NUMBER  PATH 'test1',
                         35                       test5  NUMBER  PATH 'level2/test5') x
                         36  /
                         
                        XML                                                                                   TEST1      TEST5
                        -------------------------------------------------------------------------------- ---------- ----------
                        <root><level1><test2>123</test2><test3>some string</test3></level1><level2><test                   345
                         
                        SQL> conn apps@vis
                        Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 
                        Connected as apps
                         
                        SQL> select * from v$version;
                         
                        BANNER
                        --------------------------------------------------------------------------------
                        Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
                        PL/SQL Release 11.1.0.7.0 - Production
                        CORE     11.1.0.7.0     Production
                        TNS for Linux: Version 11.1.0.7.0 - Production
                        NLSRTL Version 11.1.0.7.0 - Production
                         
                        SQL> 
                        SQL> select x.*
                          2      from   xmltable('/root' passing
                          3      xmltransform(
                          4      xmltype('<root><test1>  </test1>
                          5                                                 <level1>
                          6                                                   <test2>123 </test2>
                          7                                                   <test3>some string</test3>
                          8                                                 </level1>
                          9                                                 <level2>
                         10                                                   <test4>hi</test4>
                         11                                                   <test5>  345</test5>
                         12                                                  <test6> 2 </test6>
                         13                                                </level2>
                         14                                              </root>')
                         15                   ,
                         16                    xmltype('<?xml version="1.0"?>
                         17      <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
                         18        <xsl:output encoding="utf-8"/>
                         19        <!-- alphabetizes an xml tree -->
                         20        <xsl:template match="*">
                         21          <xsl:copy>
                         22            <xsl:apply-templates select="*|text()">
                         23              <xsl:sort select="name(.)" data-type="text" order="ascending"/>
                         24            </xsl:apply-templates>
                         25          </xsl:copy>
                         26        </xsl:template>
                         27        <xsl:template match="text()">
                         28          <xsl:value-of select="normalize-space(.)"/>
                         29        </xsl:template>
                         30      </xsl:stylesheet> ')
                         31                   )
                         32                     columns
                         33                       "XML" xmltype PATH '.',
                         34                       test1  NUMBER  PATH 'test1',
                         35                       test5  NUMBER  PATH 'level2/test5') x
                         36  /
                         
                        XML                                                                                   TEST1      TEST5
                        -------------------------------------------------------------------------------- ---------- ----------
                        <root><level1><test2>123</test2><test3>some string</test3></level1><level2><test                   345
                         
                        SQL>
                        so you can adopt it for your needs
                        but looks like it more independent from version
                        1 person found this helpful
                        • 9. Re: Remove Whitespace from Node's text
                          odie_63
                          A_Non wrote:
                          Going backwards, it is interesting that 10.2.0.4 chokes on normal-space
                          A FLWOR expression probably works in this case?

                          Not tested :
                          select result
                          from xmltable(
                                 'for $i in /root/test 
                                  return normalize-space($i)'
                                 passing XMLType('<root><test>  </test></root>')
                                 COLUMNS
                                   result  NUMBER  PATH '.'
                               ) ;
                          • 10. Re: Remove Whitespace from Node's text
                            Jason_(A_Non)
                            For the record
                            Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
                            
                            SQL> select result
                              2  from xmltable(
                              3         'for $i in /root/test
                              4          return normalize-space($i)'
                              5         passing XMLType('<root><test>  </test></root>')
                              6         COLUMNS
                              7           result  NUMBER  PATH '.'
                              8       ) ;
                             
                            ORA-19280: XQuery dynamic type mismatch: expected atomic value - got node
                            Surprised me too. I was just seeing if we had any solutions that would work for Boneist from 10.2.x.x up through 11.2.x.x
                            • 11. Re: Remove Whitespace from Node's text
                              Marco Gralike
                              Almost don't to dare anymore, but can't resist - Now being on 11.x you guys switched the CLOB columns in for Binary XML Securefile columns, right?

                              ;-)
                              • 12. Re: Remove Whitespace from Node's text
                                AlexAnd
                                I was just seeing if we had any solutions that would work for Boneist from 10.2.x.x up through 11.2.x.x
                                XMLTransform ?
                                in above example Re: Remove Whitespace from Node's text

                                so before parsing transform
                                select xmltransform(xmltype('<root><test1>  </test1>
                                                                               <level1>
                                                                                 <test2>123 </test2>
                                                                                 <test3>some string</test3>
                                                                               </level1>
                                                                               <level2>
                                                                                 <test4>hi</test4>
                                                                                 <test5>  345</test5>
                                                                                <test6> 2 </test6>
                                                                              </level2>
                                                                            </root>'),
                                   xmltype('<?xml version="1.0"?> 
                                    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
                                      <xsl:output encoding="utf-8"/>      
                                      <xsl:template match="*">  
                                        <xsl:copy>
                                          <xsl:apply-templates select="*|text()"/>           
                                        </xsl:copy> 
                                      </xsl:template>
                                      <xsl:template match="text()"> 
                                        <xsl:value-of select="normalize-space(.)"/>
                                      </xsl:template>
                                    </xsl:stylesheet> '))
                                  from dual
                                Edited by: AlexAnd on Mar 18, 2013 10:50 PM
                                • 13. Re: Remove Whitespace from Node's text
                                  odie_63
                                  Alex,

                                  Your sample stylesheet will normalize every single text() node, not only nodes supposed to be numbers (it can be easily extended to do so though), and doesn't handle possible attributes.
                                  • 14. Re: Remove Whitespace from Node's text
                                    AlexAnd
                                    Your sample stylesheet will normalize every single text() node, not only nodes supposed to be numbers (it can be easily extended to do so though), and doesn't handle possible attributes.
                                    it's sample based on doc
                                    not my :)
                                    normalize every single text() node, not only nodes supposed to be numbers
                                    yes
                                    thread is "Remove Whitespace from Node's text" ;)
                                    is it awful? as result you will have "normalize-space-xml" :)

                                    so it's may be corrected for needs

                                    why stylesheet?
                                    because it works almost the same for 10 and 11
                                    it also may be not ideal solution but as i said
                                    as idea
                                    1 2 Previous Next