11 Replies Latest reply: Jun 27, 2012 7:24 AM by 946142 RSS

    XMLCast,XMLQuery vs extractValue

    942880
      Hello fellows, newbie question -- I'm unable to find a working example to extract values using the XMLCast/XMLQuery in place of the deprecated extractValue

      Here is an XMLTYPE containing
      <CalculateFITResponse xmlns="http://tempuri.org/">
      <CalculateFITResult xmlns:a="http://schemas.datacontract.org/2004/07/ValueNavigatorService.Classes" xmlns:i="http://www.
      w3.org/2001/XMLSchema-instance">
      <a:VnSegments/>
      <a:declineRate>0</a:declineRate>
      <a:entityId>128041676</a:entityId>
      <a:eur>0</a:eur>
      <a:historyVolume>60000</a:historyVolume>
      <a:results>FitSuccessful</a:results>
      <a:segmentData xmlns:b="http://schemas.datacontract.org/2004/07/Eni.ValueNavigator.DeclineFitting"/>
      <a:segmentDataList xmlns:b="http://schemas.datacontract.org/2004/07/Eni.ValueNavigator.DeclineFitting"/>
      <a:segmentVolume>0</a:segmentVolume>
      </CalculateFITResult>
      </CalculateFITResponse>

      This function -
      CREATE OR REPLACE FUNCTION get_EUR_V_Results (CalculateFIT_OutputMessage IN XMLTYPE) RETURN VARCHAR2
      IS
      V_Result     VARCHAR2(4000)      := NULL;
      l_ns_1      VARCHAR2(4000)      := ' xmlns:a="http://schemas.datacontract.org/2004/07/ValueNavigatorService.Classes"';
      l_ns_2      VARCHAR2(4000)      := ' xmlns:i="http://www.w3.org/2001/XMLSchema-instance"';
      l_ns_3      VARCHAR2(4000)      := ' xmlns="http://tempuri.org/"';
      l_ns          VARCHAR2(4000) := l_ns_1||l_ns_2||l_ns_3;
      l_xpath     VARCHAR2(4000) := '//CalculateFITResponse//CalculateFITResult/a:';
      BEGIN
           SELECT extractValue(CalculateFIT_OutputMessage, l_xpath||'results', l_ns) INTO V_Result from dual;
      RETURN V_Result;
      END;
      /

      returns FitSuccessful

      (sucessfully I might add)

      How do I rewrite the function using XMLCast and XMLQuery?

      (Yes, I have RTFM with no headway)

      Thanks in advance,

      HKT
        • 1. Re: XMLCast,XMLQuery vs extractValue
          odie_63
          Hi,
          (Yes, I have RTFM with no headway)
          That's hard to believe...
          I found this one in less than a minute in the documentation :
          http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#BABIDFEC

          And the forum has plenty of examples too, just use the search features with XMLCast + XMLQuery + namespace keywords.
          CREATE OR REPLACE FUNCTION get_EUR_V_Results (CalculateFIT_OutputMessage IN XMLTYPE) RETURN VARCHAR2
          IS
            V_Result VARCHAR2(4000);
          BEGIN
            SELECT XMLCast(
                     XMLQuery(
                     'declare default element namespace "http://tempuri.org/"; (: :)
                      declare namespace a = "http://schemas.datacontract.org/2004/07/ValueNavigatorService.Classes"; (: :)
                      /CalculateFITResponse/CalculateFITResult/a:results'
                      passing CalculateFIT_OutputMessage
                      returning content
                     )
                     as varchar2(4000)
                   )
            INTO V_Result
            FROM dual;
          
            RETURN V_Result;
          END;
          /
          SQL> select get_EUR_V_Results(xmltype('<CalculateFITResponse xmlns="http://tempuri.org/">
            2  <CalculateFITResult xmlns:a="http://schemas.datacontract.org/2004/07/ValueNavigatorService.Classes"
            3  xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
            4  <a:VnSegments/>
            5  <a:declineRate>0</a:declineRate>
            6  <a:entityId>128041676</a:entityId>
            7  <a:eur>0</a:eur>
            8  <a:historyVolume>60000</a:historyVolume>
            9  <a:results>FitSuccessful</a:results>
           10  <a:segmentData xmlns:b="http://schemas.datacontract.org/2004/07/Eni.ValueNavigator.DeclineFitting"/>
           11  <a:segmentDataList xmlns:b="http://schemas.datacontract.org/2004/07/Eni.ValueNavigator.DeclineFitting"/>
           12  <a:segmentVolume>0</a:segmentVolume>
           13  </CalculateFITResult>
           14  </CalculateFITResponse>'))
           15  from dual;
           
          GET_EUR_V_RESULTS(XMLTYPE('<CA
          --------------------------------------------------------------------------------
          FitSuccessful
           
          • 2. Re: XMLCast,XMLQuery vs extractValue
            AlexAnd
            forum has many examples https://forums.oracle.com/forums/search.jspa?threadID=&q=xmlcast+AND+XMLQuery&objID=c84&dateRange=all&userID=&numResults=30&rankBy=10001
            with t as
            (select xmltype(
            '<CalculateFITResponse xmlns="http://tempuri.org/">
            <CalculateFITResult xmlns:a="http://schemas.datacontract.org/2004/07/ValueNavigatorService.Classes" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
            <a:VnSegments/>
            <a:declineRate>0</a:declineRate>
            <a:entityId>128041676</a:entityId>
            <a:eur>0</a:eur>
            <a:historyVolume>60000</a:historyVolume>
            <a:results>FitSuccessful</a:results>
            <a:segmentData xmlns:b="http://schemas.datacontract.org/2004/07/Eni.ValueNavigator.DeclineFitting"/>
            <a:segmentDataList xmlns:b="http://schemas.datacontract.org/2004/07/Eni.ValueNavigator.DeclineFitting"/>
            <a:segmentVolume>0</a:segmentVolume>
            </CalculateFITResult>
            </CalculateFITResponse>') xml from dual)
            --
            select xmlcast(XMLQuery('xquery version "1.0"; (: :)
            declare namespace a="http://schemas.datacontract.org/2004/07/ValueNavigatorService.Classes"; (: :)
            declare namespace i="http://www.w3.org/2001/XMLSchema-instance"; (: :)
            declare namespace s="http://tempuri.org/"; (: :)
            for $val in /s:CalculateFITResponse/s:CalculateFITResult/a:entityId 
            return $val' passing t.xml returning content) as
                            varchar2(100)) as res
              from t
            or
            SELECT val
               FROM   t
               ,    XMLTable('xquery version "1.0"; (: :)
                              declare namespace a="http://schemas.datacontract.org/2004/07/ValueNavigatorService.Classes"; (: :)
                              declare namespace i="http://www.w3.org/2001/XMLSchema-instance"; (: :)
                              declare namespace s="http://tempuri.org/"; (: :)
                              for $val in /s:CalculateFITResponse/s:CalculateFITResult/a:entityId 
                              return $val'
                              PASSING t.xml
                              columns val varchar2(30) path '.'
            ) x
            • 3. Re: XMLCast,XMLQuery vs extractValue
              942880
              Thank you much OverDose ( BTW you and Mark are doing a great job on the Forum, supporting XMMLDB)

              Practically all the examples in the forum & docs assume that the XML document is stored in a table but ours is represents a transient soap envelope in memory.

              The XMLType is the output of a call to a .NET frramework web service using the utl_dbws callout utility.

              I've switched to 64-bit 11.2.0.3 on Windows after getting ORA-04030 with 60+ iterations of the function.
              ------------------------------------------------------------------------------
              Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
              PL/SQL Release 11.2.0.1.0 - Production
              CORE 11.2.0.1.0 Production
              TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
              NLSRTL Version 11.2.0.1.0 - Production

              The PL/SQL package has to run through 2 million+ entities.

              Metalink lists a couple of XML patches but none seem to address ORA-04030.

              NAME TYPE VALUE
              ------------------------------------ ----------- -----------
              memory_max_target big integer 8160M
              memory_target big integer 8160M
              java_pool_size big integer -- 4 GB
              large_pool_size big integer 0
              olap_page_pool_size big integer 0
              shared_pool_reserved_size big integer 44459622


              Thanks again Mr OverDose
              • 4. Re: XMLCast,XMLQuery vs extractValue
                942880
                Must be doing somthing wrong --

                129 /
                ERROR:
                ORA-03114: not connected to ORACLE


                DECLARE
                *
                ERROR at line 1:
                ORA-03113: end-of-file on communication channel
                Process ID: 3928
                Session ID: 87 Serial number: 8267
                • 5. Re: XMLCast,XMLQuery vs extractValue
                  Jason_(A_Non)
                  You would probably be better off putting the response into a XMLType column (with the default BINARY XML storage) and parsing it that way. Yes, there is a bit of overhead in regards to the INSERT, but that is offset by parsing time when the XML response is large. The table could even be a TEMPORARY table too to simplify maintaining the data in it.

                  As for your
                  ORA-03113: end-of-file on communication channel
                  You'll have to look for dump files on the DB server to see the error that is causing the session to blow out.
                  • 6. Re: XMLCast,XMLQuery vs extractValue
                    942880
                    I'm trying to follow the example at Extracting of data from xml takes almost an hour for 2.5M data. .

                    Any chance of a quick and dirty sample with the above?

                    TIA,

                    HKT
                    • 7. Re: XMLCast,XMLQuery vs extractValue
                      942880
                      This helped - https://kr.forums.oracle.com/forums/thread.jspa?threadID=985576

                      CREATE TABLE tmp_xml(soap_result XMLTYPE)
                      -- XMLType STORE AS SECUREFILE BINARY XML
                      ;

                      SELECT x.entityId, x.eur, x.result
                      FROM tmp_xml t
                      , XMLTable(
                      XMLNamespaces (DEFAULT 'http://tempuri.org/',
                      'http://schemas.datacontract.org/2004/07/ValueNavigatorService.Classes' AS "a"),
                      '/CalculateFITResponse/CalculateFITResult'
                      PASSING t.soap_result
                      COLUMNS eur varchar2(30) path 'a:eur'
                      , entityId varchar2(30) path 'a:entityId'
                      , result varchar2(30) path 'a:results'
                      ) x
                      ;

                      and it works..
                      • 8. Re: XMLCast,XMLQuery vs extractValue
                        942880
                        Gurus, any ideas or suggestions?

                        create global temporary table tmp_xml (V_start timestamp, V_End timestamp, soap_result XMLTYPE);
                        INSERT INTO tmp_xml (V_start,V_End,Soap_Result) VALUES(v_start,SYSTIMESTAMP,CalculateFit_OutputMessage);

                        Row 1 EntityID - 635893
                        Row 2 EntityID - 221635
                        Row 3 EntityID - 222049
                        Row 4 EntityID - 222181
                        Row 5 EntityID - 222366
                        Row 6 EntityID - 222458
                        Row 7 EntityID - 222663
                        Row 8 EntityID - 226340
                        Row 9 EntityID - 165027
                        ORA-00600: internal error code, arguments: [kohfrm771], [], [], [], [], [], [], [], [], [], [], []

                        PL/SQL procedure successfully completed.


                        With create global temporary table tmp_xml (soap_result XMLTYPE);
                        INSERT INTO tmp_xml (Soap_Result) VALUES(CalculateFit_OutputMessage);

                        Row 1 EntityID - 635893
                        Row 2 EntityID - 221635
                        Row 3 EntityID - 222049
                        Row 4 EntityID - 222181
                        Row 5 EntityID - 222366
                        Row 6 EntityID - 222458
                        Row 7 EntityID - 222663
                        Row 8 EntityID - 226340
                        Row 9 EntityID - 165027
                        ORA-21710: argument is expecting a valid memory address of an object

                        PL/SQL procedure successfully completed.


                        Row 1 EntityID - 635893
                        Row 2 EntityID - 221635
                        Row 3 EntityID - 222049
                        Row 4 EntityID - 222181
                        Row 5 EntityID - 222366
                        Row 6 EntityID - 222458
                        Row 7 EntityID - 222663
                        Row 8 EntityID - 226340
                        Row 9 EntityID - 165027
                        ORA-21602: operation does not support the specified typecode

                        PL/SQL procedure successfully completed.
                        11.2.0.3 Patch Set - List of Bug Fixes by Problem Type [ID 1348303.1]
                        11906197 - Parallel query with COLLECT function fails with ORA-7445/ORA-600 [kohfrm771]

                        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 64-bit Windows: Version 11.2.0.3.0 - Production
                        NLSRTL Version 11.2.0.3.0 - Production
                        • 9. Re: XMLCast,XMLQuery vs extractValue
                          Jason_(A_Non)
                          It is not clear (to me at least) how that ORA-0600 error ties into those two SQL statements you provided. Given you are on 11.2.0.3, the best answer may be to open a SR with Oracle to get their assistance. We might be able to offer work-arounds if we knew more, but we can't fix the underlying cause of the error.
                          • 10. Re: XMLCast,XMLQuery vs extractValue
                            942880
                            Thanks for the look out A.Non.

                            SR SR 3-5881591951: XMLType Usage was opened with Oracle Support.

                            Using DBControl, I noticed there were an excessive number of processes running at the time - parallel_servers_target defaults to 128 on a quad core windows box.
                            http://docs.oracle.com/cd/E14072_01/server.112/e10820/initparams182.htm - I set it to 8 and reduced most of the memory paramenters

                            java_pool_size big integer 368M
                            large_pool_size big integer 0
                            shared_pool_reserved_size big integer 107374182
                            shared_pool_size big integer 512M

                            These were the changes made before these errors started showing up.

                            It's hard to see where these errors are coming from.

                            Thanks again,

                            HKT
                            • 11. Re: XMLCast,XMLQuery vs extractValue
                              946142
                              How to avoid this - the root cause seems to be XMLType usage

                              119 INSERT INTO EUR_RESULTS (Entity_ID, EnTID_Result, V_EUR, EUR_START,EUR_END)
                              120 VALUES (V_Entity_ID, V_Result,V_EUR, V_start,systimestamp);
                              121
                              122 -- IF mod(Row_ID, 100) = 0 THEN -- Commit every 100 records
                              123 commit;
                              124 -- END IF;
                              125
                              126 dbms_output.put_line('Row '||Row_ID||' '||' EntityID - '||V_Entity_ID||' EUR: '||V_EUR||' Status: '||V_Result );
                              127
                              128 FETCH get_EntIds INTO V_Entity_ID;
                              129 Row_ID := Row_ID+1;
                              130
                              131
                              132 END LOOP;
                              133 commit;
                              134 CLOSE get_EntIds;
                              135 UTL_DBWS.release_call (call_handle => l_call);
                              136 UTL_DBWS.release_service(l_service);
                              137
                              138
                              139
                              140 EXCEPTION
                              141 WHEN OTHERS THEN dbms_output.put_line(sqlerrm);
                              142 UTL_DBWS.release_call (call_handle => l_call);
                              143 UTL_DBWS.release_service(l_service);
                              144 END;
                              145 /
                              ERROR:
                              ORA-03114: not connected to ORACLE


                              DECLARE
                              *
                              ERROR at line 1:
                              ORA-03113: end-of-file on communication channel
                              Process ID: 3576
                              Session ID: 99 Serial number: 95