Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

XMLTable with multiple namespaces

ant7Apr 24 2012 — edited Apr 24 2012
I have a web service that returns the following XML:
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
   <s:Body>
      <GetGeoLocationIDByWellTagIDResponse xmlns="http://tempuri.org/">
         <GetGeoLocationIDByWellTagIDResult xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
            <a:int>9110910</a:int>
         </GetGeoLocationIDByWellTagIDResult>
      </GetGeoLocationIDByWellTagIDResponse>
   </s:Body>
</s:Envelope>
There is the possibility of more than one Int being returned, so I am trying to use xmltable to pull out this information. But I am having some difficulty pulling out the ID node, because (I am guessing) the node is named "a:int". Here is an example of how I am attempting to get this data:
select x.*
from 
(select xmltype.createxml('<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
   <s:Body>
      <GetGeoLocationIDByWellTagIDResponse xmlns="http://tempuri.org/">
         <GetGeoLocationIDByWellTagIDResult xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
            <a:int>9110910</a:int>
         </GetGeoLocationIDByWellTagIDResult>
      </GetGeoLocationIDByWellTagIDResponse>
   </s:Body>
</s:Envelope>') xml from dual) t,
xmltable(
 xmlnamespaces (
 'http://tempuri.org/' as "e",
 'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as "a"),
 '//e:GetGeoLocationIDByWellTagIDResponse/a:GetGeoLocationIDByWellTagIDResult'
 passing t.xml
 columns
 loc_id int path 'a:int'
) x;
This just returns null.

How do I references the "a:int" node in the columns clause to correctly get this record back?
This post has been answered by AlexAnd on Apr 24 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 22 2012
Added on Apr 24 2012
3 comments
31,562 views