xmltable and namespaces
Date: Thu, 3 Feb 2011 19:43:38 +0800
Message-ID: <iie4dc$c2i$1_at_speranza.aioe.org>
Hi,
The following code will stop working if I remove the (xmlns=""). As I don't control the web-service that generates the XML (and omits the null namespace specification) how can I persuade Oracle to parse the XML correctly so as to pluck out the EmployeeID attribute?
The only other way (after many, many incantations) that I got it to work was introducing a new xmltable for every level/qualification. Can someone please provide an alternative that does not require the xmlns="" property?
Cheers Richard Maher
declare
soap_resp varchar(32767);
resp sys.xmltype;
begin
soap_resp := '<GetStaffDetailsResponse xmlns="https://www.axby.com/staffQuery/">
<GetStaffDetailsResult>
<StaffDetails xmlns="">
<AuEduPersonPreferredGivenName>Bruce</AuEduPersonPreferredGivenName>
<AuEduPersonPreferredSurname>Coad</AuEduPersonPreferredSurname>
<EmployeeID>000715BC</EmployeeID>
</StaffDetails>
</GetStaffDetailsResult>
</GetStaffDetailsResponse>';
resp := xmltype.createxml(soap_resp);
for i in (
select
Staff.PersonId
from
xmltable
( XmlNamespaces('https://www.axby.com/staffQuery/' as SQ),
'/SQ:GetStaffDetailsResponse'
passing resp
columns
xmlresult xmltype path 'SQ:GetStaffDetailsResult/StaffDetails'
) soapXML
, xmltable
( '*'
passing soapXML.xmlresult
columns
PersonId varchar2(8) path 'EmployeeID'
) Staff )
loop
dbms_output.put_line('hello '||i.PersonID);
end loop;
end; Received on Thu Feb 03 2011 - 05:43:38 CST