Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

XML Value extraction just not working with solutions I've found.

Mark WarnerSep 7 2017 — edited Sep 8 2017

I have XMLtype variable which is returned from a RESTful API call to a third party app.  The XML seems to be formatted correctly, but all solutions I've tried that I've found here just aren't working for me..  I'm hoping it's something simple (and probably not so smart) that I'm doing wrong..

The XML file is as follows:

<?xml version="1.0" encoding="UTF-8"?>

<a:persons xmlns:a="http://model.hrdataservice.hrp.somecompany.com">

   <person>

      <personId>79563</personId>

      <perOrg>EMP</perOrg>

      <poiType />

      <firstName>Tim</firstName>

      <legalFirstName>Timothy</legalFirstName>

      <lastName>Jones</lastName>

      <emailAddress>testemail@somecompany.com</emailAddress>

   </person>

</a:persons>

I'm trying to retrieve the element values (I.e. personID)  The following is the stored procedure I'm using:

create or replace procedure hrdataapp_sp
( p_job_run_id in number,
  p_job_user_id in number )
is
  req utl_http.req;
  res utl_http.resp;
  url varchar2(4000) := 'http://webcat3.somecompany.com/HRDataService/api/person/search?q=79563';
  name varchar2(4000);
  buffer varchar2(4000);
  bufferxml xmltype;

  v_personID                  varchar2(10);
  v_perOrg                    varchar2(10);
  v_poiType                   varchar2(50);
  v_firstName                 varchar2(30);
  v_legalFirstName            varchar2(30);
  v_middleName                varchar2(30);
  v_lastName                  varchar2(30);
  v_emailAddress              varchar2(50);

-- Content being passed
  content varchar2(4000) := NULL;

begin

dbms_output.put_line(content);

  req := utl_http.begin_request(url, 'GET', 'HTTP/1.1');
  utl_http.set_header(req, 'User-Agent', 'mozilla/4.0');
  utl_http.set_header(req, 'Content-Type', 'application/json');
  utl_http.set_header(req, 'x-application-name', 'test-search-interface');
  utl_http.set_header(req, 'Content-Length', length(content));
  utl_http.write_text(req, content);
  res := utl_http.get_response(req);

  begin
    loop
      utl_http.read_line(res, buffer);
      dbms_output.put_line(buffer);
    end loop;
    utl_http.end_response(res);
  exception
    when utl_http.end_of_body
    then
      utl_http.end_response(res);
  end;

-- process the response from the HTTP call

with mytable as (
   select xmltype(buffer) mycolumn
     from dual)
select x.personID, x.firstName
  into v_personID, v_firstName
  from mytable t,
       xmltable(
         '/persons'
         passing t.mycolumn
         columns personID varchar2(10) path '@personID',
                 firstName varchar2(30) path '@firstName'
       ) x
  ;

dbms_output.put_line('personID: ' || v_personID || '  firstName: ' || v_firstName);

end hrdataapp_sp;
/
show errors;

When the procedure runs, I get a "ORA-19032 Expected XML tag, got no content"  error message

This post has been answered by Paulzip on Sep 8 2017
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 6 2017
Added on Sep 7 2017
2 comments
532 views