3 Replies Latest reply: Nov 11, 2013 6:48 PM by Oracleator RSS

    Extract XML Value returns nothing

    Oracleator

      Hi, I have a table where I have put one XMLTYPE column called XML_RESPONSE and one CLOB called XML_RESPONSE_CLOB with the same XML content each.

       

      One of the records values in both columns look like this:

       

      <?xml version='1.0' encoding='UTF-8'?><S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/"><S:Body><ns2:StampCFDBytesResponse xmlns:ns2="http://impl.controllers.massive.fe.sto.com/" xmlns:ns3="http://exception.fe.sto.com/"><stampedDocument>PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiPz4KPGNmZGk6Q29tcHJvYmFudGUgdmVyc2lvbj0iMy4yIiBmb2xpbz0Cinb2IubXgvVGltYnJlRmlzY2FsRGlnaXRhbCBodHRwOi8vd3d3LnNhdC5nb2IubXgvVGltYnJlRmlzY2FsRGlnaXRhbC9UaW1icmVGaXNjYWxEaWdpdGFsLnhzZCIgeG1sbnM6dGZkPSJodHRwOi8vd3d3LnNhdC5nb2IubXgvVGltYnJlRmlzY2FsRGlnaXRhbCIvPgogICAgPC9jZmRpOkNvbXBsZW1lbnRvPgo8L2NmZGk6Q29tcHJvYmFudGU+</stampedDocument></ns2:StampCFDBytesResponse></S:Body></S:Envelope>

       

      I am trying to extract the base64 code which is inside the <stampedDocument> element.

       

      If I use this code the procedure runs but returns nothing:

       

      select apex_web_service.parse_xml (XML_RESPONSE, '//Envelope/Body/stampedDocument')

      into v_stamped from f_cfdi_timbrados where id_cfdi_timbrado = 1002;

       

      If I use any of the following options the procedure returns an error:

       

      select apex_web_service.parse_xml (XML_RESPONSE, '//Envelope/Body/stampedDocument/stampedDocument()')

      select apex_web_service.parse_xml (XML_RESPONSE, '//S:Envelope/S:Body/stampedDocument/stampedDocument()')

      select apex_web_service.parse_xml (XML_RESPONSE, '//S:Envelope/S:Body/stampedDocument')

       

      into v_stamped from f_cfdi_timbrados where id_cfdi_timbrado = 1002;

       

      06503. 00000 - "PL/SQL: Function returned without value"

      *Cause: A call to PL/SQL function completed, but no RETURN statement was

        executed.

      *Action: Rewrite PL/SQL function, making sure that it always returns

        a value of a proper type.

       

      I have also tried with the function ExtractValue and retunrs null or error.

       

      SELECT id_cfdi_timbrado,

        EXTRACTVALUE(e.xml_response, '/Envelope/Body/stampedDocument')

        "CFDI_BASE_64"

        FROM f_cfdi_timbrados e

        WHERE id_cfdi_timbrado = 1002;

       

      Please advise. This is my first experience parsing XML and it seems I am lost.

       

      Francisco

        • 1. Re: Extract XML Value returns nothing
          Jason_(A_Non)

          The error message is providing the clue that you need.  If you were to search on that error message you would see that it is trying to tell you that you are missing a RETURN statement at the end of your function.  Something like

          RETURN v_stamped;

           

          That would resolve the issue causing the ORA error.

           

          As for the second issue you will run into, your extracts are not properly handling the namespace that is part of the XML.  For extractValue, there is a third parm

          EXTRACTVALUE

          so your query would look like

          EXTRACTVALUE(e.xml_response, '/S:Envelope/S:Body/ns2:stampedDocument', ' xmlns:S="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns2="http://impl.controllers.massive.fe.sto.com/"')

          I'll leave it up to you to research apex_web_service.parse_xml

           

          That said, depending upon your version of Oracle, extractValue could be deprecated and you may need to go another route.

           

          Here are some tips for posting on the forums

          Re: 2. How do I ask a question on the forums?

          • 2. Re: Extract XML Value returns nothing
            Oracleator

            Hi Jason,

             

             

            Thanks for your reply.

             

             

            Sorry for the missing information on my initial post. Database version is: 11.2.0.1.0

             

             

            I redesigned my code based on your recommendations. It seems I am getting closer to have a response, but both functions say the value is too large.

             

             

            OPTION 1.

             

            declare

            v_id_cfdi_timbrado number;

            v_cfdi_base_64 clob;

             

             

            begin

            SELECT id_cfdi_timbrado,

              EXTRACTVALUE(e.xml_response, '/S:Envelope/S:Body/ns2:StampCFDBytesResponse/stampedDocument', ' xmlns:S="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns2="http://impl.controllers.massive.fe.sto.com/"')

              into v_id_cfdi_timbrado, v_cfdi_base64

              FROM f_cfdi_timbrados e

              WHERE id_cfdi_timbrado = 1002;

            end;

             

             

            retunrs: ORA-01706: "user function result value was too large"

             

             

            OPTION 2.

             

            declare

            v_stamped clob;

             

             

            begin

            select apex_web_service.parse_xml (p_xml => XML_RESPONSE,

            p_xpath => '//S:Envelope/S:Body/ns2:StampCFDBytesResponse/stampedDocument',

            p_ns => 'xmlns:S="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns2="http://impl.controllers.massive.fe.sto.com/"')

            into v_stamped from f_cfdi_timbrados where id_cfdi_timbrado = 1002;

            dbms_output.put_line(v_stamped);

            end;

             

            returns: ORA-06502: PL/SQL: error : character string buffer too small - numeric or value error%s

             

             

            The base64 text inside the stampedDocument element in the original XML is more than 7,000 characters long (I pasted just a fragment in my orginal post), but that should be managed by a clob variable. Am I right?

             

             

            I have included the text() element after stampedDocument based on one post I just read, but nothing changed.

             

            I have also included the RETURN v_stamped sentence but it leads to another error. Seems because this is a procedure, not a function. But I don't think a function will do things different. Anyway, that error is not appearing anymore.

             

             

            Any other idea?

             

             

            Francisco

            • 3. Re: Extract XML Value returns nothing
              Oracleator

              Hi Jason,

               

              I had been working for days on this problem. Now it is solved! Thanks to you.

               

              From my last reply, I used OPTION 2 but  with function apex_web_service.parse_xml_blob instead of apex_web_service.parse_xml. And it works nicely!

               

              The secret was just to know how to populate the parameter p_ns.

               

              Thanks again.

               

              Francisco