CODEWITHSUNDEEP
xmloracle-databaseoracle11g
Ali Asgor
Ali Asgor

Reputation: 61

Query value from XMLtype column in oracle database

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?

Upvotes: 0

Views: 1897

Answers (1)

Boneist
Boneist

Reputation: 23578

EXTRACT (and EXTRACTVALUE) are deprecated in 10g and above. You should be using XMLTABLE instead:

with xml_table as (select 1 id, 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>') xml_data from dual)
select x.*
from   xml_table xt,
       xmltable('/current' passing xt.xml_data
                columns country varchar2(10) path 'city/country',
                        temp_k number path 'temperature/@value') x;

COUNTRY        TEMP_K
---------- ----------
GB             280.71

Upvotes: 4

Related Questions