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