DevOps, CI/CD and Automation

Hi,

I have a piece of XML document that have this format (working with Oracle RDBMS 12c):

LOCAL_XML

--------------------------------------------------------------------------------

<?xml version="1.0" encoding="US-ASCII"?>

<GeocodeResponse>

  <status>OK</status>

  <result>

    <type>street_address</type>

    <formatted_address>Via Pasubio, 34, 12025 Dronero CN, Italy</formatted_address>

    <address_component>

      <long_name>34</long_name>

      <short_name>34</short_name>

      <type>street_number</type>

    </address_component>

  ...

  </result>

  <result>

    <type>locality</type>

    <type>political</type>

    <formatted_address>12025 Dronero, Province of Cuneo, Italy</formatted_address>

    <address_component>

      <long_name>Dronero</long_name>

      <short_name>Dronero</short_name>

      <type>locality</type>

      <type>political</type>

    </address_component>

    <address_component>

...

I wish to extract the first occurence of the element <formatted_address>, that is in this example: Via Pasubio, 34, 12025 Dronero CN, Italy.

That piece of XML is defined as XMLtype column (geo_xml), in table t_geo_xmltype2.

select

    extractvalue(a.geo_xml, '/GeocodeResponse/result/formatted_address') as addr

from

    t_geo_xmltype2 a;

As starting point, the query above returns this error:

ERROR at line 4:

ORA-19025: EXTRACTVALUE returns value of only one node

How can I extract just the first value encountered for element <formatted_address>?

Thanks by advance for any tip.

Kind Regards