ORACLE EXTRACT()和EXTRACTVALUE() 使用

drop table t;

create table t(text xmltype);

INSERT INTO t VALUES (XMLType.CreateXML(
'<?xml version="1.0"?>
<fall>
<name>dingjun123</name>
<county>china</county>
<state>xxx</state>
<url>
http://michiganwaterfalls.com/autrain_falls/autrain_falls.html
</url>
</fall>'));

INSERT INTO t VALUES (XMLType.CreateXML(
'<?xml version="1.0"?>
<fall>
<name>jack</name>
<county>china</county>
<state>yyyy</state>
<url>
http://michiganwaterfalls.com/autrain_falls/autrain_falls.html
</url>
</fall>'));
commit;

select extractValue(value(m), '/fall/name') name,
extractValue(value(m), '/fall/county') county,
extractValue(value(m), '/fall/state') state
      from t,
           table( xmlsequence(extract(t.text,'/fall'))) m ;

参考链接:http://www.itpub.net/thread-1477782-1-1.html