SQL extract

最新推荐文章于 2023-12-12 23:03:36 发布

GaoY

最新推荐文章于 2023-12-12 23:03:36 发布

阅读量843

点赞数

文章标签： sql insert object table each null

本文链接：https://blog.csdn.net/snowgy/article/details/5790238

版权

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()

GaoY

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL extract

<br />extract with dates Which year did we have 300 days ago? select extract (year from sysdate-300) from dual;<br /> What Month did we have 10 days ago? select extract(month from sysdate-10) from dual;<br /> What day is to
复制链接

扫一扫