7 Replies Latest reply on Jun 6, 2012 9:33 AM by 941221

    xml extract problem

    941221
      I have a problem about sql to extract xml node,the sql followed:
      select a.disptx.extract('/Record/_20/text()').getStringVal() from swifti a where swidty = '6666666666666666666'

      and the clob is :
      <Record>
      <swrctm>2223</swrctm>
      <swintm>22:23:13</swintm>
      <swindt>20100826</swindt>
      <fmbank>ssssss</fmbank>
      <tobank>444444rrrrrrr</tobank>
      <fm>sdfds</fm>
      <to>wwwwwwwwww</to>
      <mgtype>103</mgtype>
      <swftid>dddddd</swftid>
      <sw108f>dddddd</sw108f>
      <swackf>0</swackf>
      <_20>fffffffffff</_20>
      </Record>

      that throw the exception:
      ORA-31011: XML parsing failed
      ORA-19202: Error occurred in XML processing
      LPX-00601: Invalid token in: '/Record/_20/text()'
      ORA-06512: at "SYS.XMLTYPE", line 111
      31011. 00000 - "XML parsing failed"
      *Cause:    XML parser returned an error while trying to parse the document.
      *Action:   Check if the document to be parsed is valid.

      can help me?
        • 1. Re: xml extract problem
          AlbertoFaenza
          Hi let,

          I've made a quick test:
          /* Formatted on 06/06/2012 10:31:37 (QP5 v5.149.1003.31008) */
          WITH swifti AS (
             SELECT xmltype (
          '<Record>
          <swrctm>2223</swrctm>
          <swintm>22:23:13</swintm>
          <swindt>20100826</swindt>
          <fmbank>ssssss</fmbank>
          <tobank>444444rrrrrrr</tobank>
          <fm>sdfds</fm>
          <to>wwwwwwwwww</to>
          <mgtype>103</mgtype>
          <swftid>dddddd</swftid>
          <sw108f>dddddd</sw108f>
          <swackf>0</swackf>
          <_20>fffffffffff</_20>
          </Record>') disptx FROM DUAL)
          SELECT a.disptx.EXTRACT ('/Record/_20/text()').getstringval ()
            FROM swifti a;
          
          and here is the output:
          
          A.DISPTX.EXTRACT('/RECORD/_20/TEXT()').GETSTRINGVAL()
          --------------------------------------------------------------------------------
          fffffffffff
          However you might have a look at this thread: [url:https://forums.oracle.com/forums/thread.jspa?threadID=2314696]Error when reading of XML file

          Regards.
          Al
          • 2. Re: xml extract problem
            Igor.M
            you need convert clob to xmltype
            select xmltype(a.disptx).extract('/Record/_20/text()').getStringVal() from swifti a
            create table test (x clob);
            
            Table created.
            
            insert into test values ('<Record>
            <swrctm>2223</swrctm>
            <swintm>22:23:13</swintm>
            <swindt>20100826</swindt>
            <fmbank>ssssss</fmbank>
            <tobank>444444rrrrrrr</tobank>
            <fm>sdfds</fm>
            <to>wwwwwwwwww</to>
            <mgtype>103</mgtype>
            <swftid>dddddd</swftid>
            <sw108f>dddddd</sw108f>
            <swackf>0</swackf>
            <_20>fffffffffff</_20>
            </Record>');
            commit;
            
            
            1 row created.
            Commit complete.
            
            
            SELECT xmltype(x).EXTRACT ('/Record/_20/text()').getstringval ()
              FROM test;
            
            XMLTYPE(X).EXTRACT('/RECORD/_20/TEXT()').GETSTRINGVAL()                         
            --------------------------------------------------------------------------------
            fffffffffff                                                                     
            1 row selected.
            http://docs.oracle.com/cd/B10500_01/appdev.920/a96620/xdb04cre.htm
            • 3. Re: xml extract problem
              941221
              first of all,thanks.
              but the problem exsit still, do have the possibility that's the oracle db version is too low?
              • 4. Re: xml extract problem
                odie_63
                Works for me as well.

                What's the database version? (select * from v$version)

                I don't think this is the problem here, but to extract scalar values, one must use extractValue() function (or XMLCast/XMLQuery in 11.2) as it takes care of converting character entities back to their readable form (if any) and return the correct datatype in case the XML is schema-based.

                And please clarify : what's the datatype of DISPTX?

                Edited by: odie_63 on 6 juin 2012 10:53
                • 5. Re: xml extract problem
                  941221
                  the version :Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

                  and the disptx is clob

                  but if i excute "select extractvalue(a.disptx,'/Record/swrctm') value from swifti a where swidty ='666666666666666666'",it work well,why?

                  if i add the underscore ,the problem appear.
                  • 6. Re: xml extract problem
                    odie_63
                    and the disptx is clob
                    Are you sure?
                    If it were CLOB, the query wouldn't pass datatype checking, resulting in a different error.
                    Please always be precise so that we can focus on real issues.

                    What does this return?
                    select data_type 
                    from user_tab_columns 
                    where table_name = 'SWIFTI' 
                    and column_name = 'DISPTX';
                    if i add the underscore ,the problem appear.
                    Probably a bug in your version then, I can't reproduce on 10.2.0.5 which is the closer I have right now.
                    • 7. Re: xml extract problem
                      941221
                      thanks ,maybe it is the version problem

                      if execute "select extractvalue(xmltype(disptx),'/Record/swrctm') from swifti where swidty = '666666666666'"

                      the output is :
                      extractvalue(xmltype(disptx),'
                      2223

                      and execute "select extractvalue(disptx,'/Record/_20') from swifti where rownum<10 and swidty = '6666666666666'"

                      the output is :
                      ORA-31011:XML parsing failed
                      ORA-19202:Error occurred in XML processing
                      LPX-00601:Invalid token in :'/Record/_20'