xmltable and namespaces

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