I am trying to extract a value from an xml element, located in an XMLTYPE column in an Oracle Table. My Function load_xml return the xml looks something like:
<?xml version="1.0" encoding="utf-8"?>
<ymaps xmlns="http://ift.tt/1nVNXMM" xmlns:xsi="http://ift.tt/ra1lAU" xsi:schemaLocation=" http://ift.tt/1kwHWWc http://ift.tt/1nVO0bn http://ift.tt/1BjqejM http://ift.tt/1nVNY34 http://ift.tt/1BjqeA9 http://ift.tt/1nVO0bs http://ift.tt/1BjqeAb http://ift.tt/1BjqeAd http://ift.tt/1BjqeAf http://ift.tt/1Bjqhfe http://ift.tt/1nVNY3b http://ift.tt/15VPpxi">
<Attribution xmlns="http://ift.tt/1nVNY3d"/>
<GeoObjectCollection>
<metaDataProperty xmlns="http://ift.tt/WaETtx">
<GeocoderResponseMetaData xmlns="http://ift.tt/1BjqejM">
<request>Санкт-Петербург,Кронверкский проспект,27</request>
<found>1</found>
<results>10</results>
</GeocoderResponseMetaData>
</metaDataProperty>
<featureMember xmlns="http://ift.tt/WaETtx">
<GeoObject xmlns="http://ift.tt/1nVNXMM">
<metaDataProperty xmlns="http://ift.tt/WaETtx">
<GeocoderMetaData xmlns="http://ift.tt/1BjqejM">
<kind>house</kind>
<text>Россия, Санкт-Петербург, Кронверкский проспект, 27</text>
<precision>exact</precision>
<AddressDetails xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0">
<Country>
<AddressLine>Санкт-Петербург, Кронверкский проспект, 27</AddressLine>
<CountryNameCode>RU</CountryNameCode>
<CountryName>Россия</CountryName>
<AdministrativeArea>
<AdministrativeAreaName>Северо-Западный федеральный округ</AdministrativeAreaName>
<SubAdministrativeArea>
<SubAdministrativeAreaName>Санкт-Петербург</SubAdministrativeAreaName>
<Locality>
<LocalityName>Санкт-Петербург</LocalityName>
<Thoroughfare>
<ThoroughfareName>Кронверкский проспект</ThoroughfareName>
<Premise>
<PremiseNumber>27</PremiseNumber>
</Premise>
</Thoroughfare>
</Locality>
</SubAdministrativeArea>
</AdministrativeArea>
</Country>
</AddressDetails>
</GeocoderMetaData>
</metaDataProperty>
<description xmlns="http://ift.tt/WaETtx">Санкт-Петербург, Россия</description>
<name xmlns="http://ift.tt/WaETtx">Кронверкский проспект, 27</name>
<boundedBy xmlns="http://ift.tt/WaETtx">
<Envelope>
<lowerCorner>30.309167 59.953241</lowerCorner>
<upperCorner>30.325624 59.961494</upperCorner>
</Envelope>
</boundedBy>
<Point xmlns="http://ift.tt/WaETtx">
<pos>30.317395 59.957368</pos>
</Point>
</GeoObject>
</featureMember>
</GeoObjectCollection>
</ymaps>
I want to extract the content of the "pos" element. But the script below does not output anything.
DECLARE
TEXT CLOB;
POS VARCHAR2(50);
BEGIN
DBMS_OUTPUT.ENABLE;
TEXT := load_xml('http://ift.tt/15VPr8x');
SELECT extractValue(VALUE(t),'/ymaps/GeoObjectCollection/featureMember/GeoObject/Point/pos') INTO POS
FROM TABLE(XMLSequence(XMLType(TEXT))) t;
DBMS_OUTPUT.put_line(POS);
END;
/
Please help me.
No comments:
Post a Comment