SQL & PL/SQL

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!

Extract value from xml type column in oracle database 11gR2

Ali AsgorMar 4 2015 — edited Mar 5 2015

I have a XMLtype column containing table like `XML_TABLE (ID NUMBER, XML_DATA XMlTYPE)`.

Then i insert a value like

INSERT INTO xml_table (1, XMLtype('<current>
      <city id="2643743" name="London">
        <coord lon="-0.13" lat="51.51"/>
        <country>GB</country>
        <sun rise="2015-03-04T06:38:20" set="2015-03-04T17:46:01"/>
      </city>
      <temperature value="280.71" min="280.15" max="281.15" unit="kelvin"/>
      <humidity value="77" unit="%"/>
      <pressure value="1029" unit="hPa"/>
    </current>'));

Now i want to query on this table. i can select easily 'country' by following query

select t.xml_data.extract('/current/city/country/text()').getStringVal() "XML Data"
from xml_table t;

But i can't select temperature value by this query. Now how to select temperature value from the table?

This post has been answered by odie_63 on Mar 4 2015

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 2 2015

Added on Mar 4 2015

4 comments

1,082 views