1 Reply Latest reply: Aug 25, 2013 4:47 AM by odie_63 Branched from an earlier discussion. RSS

    Re: Extracting the data from web service response

    form_dev

      Hi Odie,

       

      I am getting an runtime  error when I  ran this  procedure. Also noticed that,   if I  remove all prefix from p_path variable,  I do  not get any runtime error but the result are blanks.

      Would apprecaite, any  help.

       

       

      ===============================================

      ORA-19112: error raised during evaluation:

      XVM-01081: [XPST0081] Invalid prefix

      1   /soap:Envelope/soap:Body/ns0:GetOptOutAllResponse/ns0:GetOptOutAllResult/di

      -   ^

      ============================================

       

       

      create or replace    procedure temp34 as

      p_path varchar2(400)  := '/soap:Envelope/soap:Body/ns0:GetOptOutAllResponse/ns0:GetOptOutAllResult/diffgr:diffgram/NewDataSet/OptOutAll';

      begin

      for r in (

      SELECT x.*

           FROM tmp_xml t

              , XMLTable(

                  XMLNamespaces(

                    'http://www.w3.org/2003/05/soap-envelope' as "soap"

                  , 'urn:schemas-microsoft-com:xml-diffgram-v1' as "diffgr"

                  , 'http://api.***.com/' as "ns0"

                  )

                , p_path

                 PASSING t.XML

                 COLUMNS OptOutTime  TIMESTAMP WITH TIME ZONE PATH 'OptOutTime'

                     , MailingList VARCHAR2(100)            PATH 'MailingList'

                      , Reason      VARCHAR2(150)            PATH 'Reason'

              ) x

               )

               loop

               dbms_output.put_line(r.MailingList );

               end loop;

       

      end;

       

      Thanking you, in advance.

        • 1. Re: Extracting the data from web service response
          odie_63

          Hi,

           

          Any good reasons to use a dynamic XQuery expression via a PL/SQL variable?

           

          It's not a good idea for two reasons :

          1. It doesn't work with the XMLNamespaces clause, it is simply ignored.
          2. It disables parse-time optimization, so no streaming evaluation even tough you're using a binary xmltype source.

           

          Use a static XQuery string as shown in the beginning of this thread.

          Or, if you really need a variable expression you'll have to declare the namespace mappings in it as well :

          'declare namespace soap = "http://www.w3.org/2003/05/soap-envelope";

          declare namespace diffgr = "urn:schemas-microsoft-com:xml-diffgram-v1";

          declare namespace ns0 = "http://api.***.com/";

          /soap:Envelope/soap:Body/ns0:GetOptOutAllResponse/ns0:GetOptOutAllResult/diffgr:diffgram/NewDataSet/OptOutAll'

          but again that usage is discouraged.

          Also note that declaring namespaces that way only apply to the scope of the main XQuery expression, so if there are any namespace-qualified PATH expressions in the COLUMNS clause, it won't work either.