René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

extract [Oracle SQL]

extract with dates

Which year did we have 300 days ago?
select extract (year from sysdate-300) from dual;
What Month did we have 10 days ago?
select extract(month from sysdate-10) from dual;
What day is today?
select extract(day from sysdate) from dual;

extract with XML

extract(xml-type-instance, 'xpath-expression')
extract(xml-type-instance, 'xpath-expression', 'name-space')
extract is similar to existsNode()

An example

create table some_things of xmltype;
Three xml documents are inserted into the table.
insert into some_things values (xmltype('
  <things>
    <numbers><item>1</item><item>59</item></numbers>
    <animals><item>bird</item><item>cat</item><item>dog</item></animals>
  </things>
'));

insert into some_things values (xmltype('
  <things>
    <countries><item>Canada</item><item>Egypt</item><item>Italy</item></countries>
    <numbers><item>55</item><item>101</item></numbers>
  </things>
'));

insert into some_things values (xmltype('
  <things>
    <cities><item>New York</item><item>Tokyo</item><item>Zurich</item></cities>
    <animals><item>elephant</item><item>snake</item></animals>
  </things>
'));
Selecting the 2nd animal in each document:
select extract(object_value,'/things/animals/item[position()=2]') "2nd Animal" from some_things;
2nd Animal
------------------------------------------------------------
<item>cat</item>

<item>snake</item>
The 2nd inserted document doesn't have an animal, so null is returned instead.
Similar to existsNode()