Query value from XMLtype column in oracle database

Question

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?

Boneist · Accepted Answer · 2015-03-04 12:32:30Z

4

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

answered Mar 4, 2015 at 12:32

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query value from XMLtype column in oracle database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related