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;

declare
soap_resp varchar(32767);
resp sys.xmltype;
begin
soap_resp := '<GetStaffDetailsResponse
xmlns="https://www.axby.com/staffQuery/">
<GetStaffDetailsResult>
<StaffDetails>
<AuEduPersonPreferredGivenName>Bruce</AuEduPersonPreferredGivenName>
<AuEduPersonPreferredSurname>Coad</AuEduPersonPreferredSurname>
<EmployeeID>000715BC</EmployeeID>
</StaffDetails>
</GetStaffDetailsResult>
</GetStaffDetailsResponse>';

resp := xmltype.createxml(soap_resp);

for r in (
select
extractvalue(
resp,
'/ns:GetStaffDetailsResponse/ns:GetStaffDetailsResult/ns:StaffDetails/ns:EmployeeID',
'xmlns:ns="https://www.axby.com/staffQuery/"') X
from dual
) loop
dbms_output.put_line('Hello:' || r.X);
end loop;
end;
/

this will stop working of you have more than one employee, but then you should look at xmlsequence

declare
soap_resp varchar(32767);
resp sys.xmltype;
begin
soap_resp := '<GetStaffDetailsResponse
xmlns="https://www.axby.com/staffQuery/">
<GetStaffDetailsResult>
<StaffDetails>
<AuEduPersonPreferredGivenName>Bruce</AuEduPersonPreferredGivenName>
<AuEduPersonPreferredSurname>Coad</AuEduPersonPreferredSurname>
<EmployeeID>000715BC</EmployeeID>
</StaffDetails>
</GetStaffDetailsResult>
</GetStaffDetailsResponse>';

resp := xmltype.createxml(soap_resp);

for r in (
select
extractvalue(
resp,
'/GetStaffDetailsResponse/GetStaffDetailsResult/StaffDetails/EmployeeID',
'xmlns="https://www.axby.com/staffQuery/"') X
from dual
) loop
dbms_output.put_line('Hello:' || r.X);
end loop;
end;
/


Thanks to Flavio Casetta's "Annals of Oracle's Improbable Errors" :
http://oraclequirks.blogspot.com/200...d-default.html

"Gerard H. Pille" <ghp (AT) skynet (DOT) be> wrote

Hi Gerard,

Thanks for the reply.

Not wanting to take anything away from Flavio but, yes, we do need to have
repeating StaffDetails tags. Can anyone help with this?

Surely ther is just some simple "a/b/c" qualification or syntax/switch?

Cheers Richard Maher

declare
soap_resp varchar(32767);
resp sys.xmltype;
begin
soap_resp :=
'<GetStaffDetailsResponse'
resp := xmltype.createxml(soap_resp);

for r in (
select
extractvalue(
value(Y),
'/StaffDetails/EmployeeID',
'xmlns="https://www.axby.com/staffQuery/"') X
from
table(
xmlsequence(
extract(
resp,
'/GetStaffDetailsResponse/GetStaffDetailsResult/
StaffDetails',
'xmlns="https://www.axby.com/staffQuery/"'
)
)
) Y
) loop
dbms_output.put_line('Hello:' || r.X);
end loop;
end;
/

"Gerard H. Pille" <ghpille (AT) hotmail (DOT) com> wrote

Now that looks good! I'll give it a go on Monday. Thanks Gerard.

Cheers Richard Maher