xmltable and namespaces

From: Richard Maher <maher_rj_at_hotspamnotmail.com>
Date: Thu, 3 Feb 2011 19:43:38 +0800
Message-ID: <iie4dc$c2i$1_at_speranza.aioe.org>


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


    soap_resp varchar(32767);

    resp sys.xmltype;


  soap_resp := '<GetStaffDetailsResponse xmlns="https://www.axby.com/staffQuery/">


    <StaffDetails xmlns="">







    resp := xmltype.createxml(soap_resp);

    for i in (





           ( XmlNamespaces('https://www.axby.com/staffQuery/' as SQ),


              passing resp


              xmlresult xmltype path 'SQ:GetStaffDetailsResult/StaffDetails'

           ) soapXML

, xmltable

           ( '*'

              passing soapXML.xmlresult


              PersonId varchar2(8) path 'EmployeeID'

           ) Staff )


           dbms_output.put_line('hello '||i.PersonID);

        end loop;

end; Received on Thu Feb 03 2011 - 05:43:38 CST

Original text of this message