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

    Re: Extracting the data from web service response


      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';


      for r in (

      SELECT x.*

           FROM tmp_xml t

              , XMLTable(


                    '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



               dbms_output.put_line(r.MailingList );

               end loop;




      Thanking you, in advance.

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



          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/";


          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.