0

script return error

v_xml := XMLTYPE(UTL_HTTP.REQUEST(URL => 'http://www.nbg.ge/rss.php'));

enter image description here

the CDATA length is 6227, how I can extract data from this XML

1
  • 4
    Please try to build a minimal reproducible example that can help people to understand and replicate your issue and help you. Commented Jul 19, 2018 at 8:56

1 Answer 1

1

Code to replicate this problem:

declare
  v_xml xmltype;
begin
  v_xml := XMLTYPE(UTL_HTTP.REQUEST(URL => 'http://www.nbg.ge/rss.php'));
end;

However there seems to be nothing wrong with the XML itself. If I put the URL in a browser and get the XML there. It will parse into an XMLTYPE.

declare
  v_xml xmltype ;
begin
  v_xml  :=  xmltype('<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0">
   <channel>
      <title>RSS NBG Currency Rates</title>
      .... rest of the xml ....
   </channel>
</rss>
');
end;

Problem is probably with getting a long string from the request. The following code will take care of it reading the data in chunks of 2000.

declare
   l_clob                  clob;
   l_req                   utl_http.req;
   l_resp                  utl_http.resp;
   l_buffer                varchar2(4096);
   l_pos                   pls_integer;
   l_xml   xmltype;

begin

   dbms_lob.createtemporary(lob_loc => l_clob, cache => true, dur => dbms_lob.call);

   -- -----------------------------------
   -- OPEN TEMPORARY LOB FOR READ / WRITE
   -- -----------------------------------
   dbms_lob.open(lob_loc => l_clob, open_mode => dbms_lob.lob_readwrite);

   l_req := utl_http.begin_request('http://www.nbg.ge/rss.php');

   utl_http.set_header(l_req, 'Content-Type', 'text/xml; charset=utf-8');
   utl_http.set_header(l_req, 'User-Agent', 'Mozilla/4.0');
   l_resp := utl_http.get_response(l_req);

   begin
      loop
         utl_http.read_text(r => l_resp, data => l_buffer, len => 2000);

         dbms_lob.writeappend(lob_loc => l_clob, amount => length(l_buffer), buffer => l_buffer);

      end loop;
   exception
      when utl_http.end_of_body then
         null;
      when others then
         raise;
   end;

   utl_http.end_response(l_resp);

   l_xml := xmltype(l_clob);

end;
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.