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?