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
