create table xmlexample(
ID varchar2(10),
name varchar2(20),
data xmltype
)
;
insert into xmlexample(id,name,data)
values(1,'my document',
sys.xmltype.createxml('<?xml version="1.0" encoding="UTF-8" ?>
<collection xmlns="">
<record>
<leader>-----nam0-22-----^^^450-</leader>
<datafield tag="200" ind1="1" ind2=" ">
<subfield code="a">抗震救灾</subfield>
<subfield code="f">奥运会</subfield>
</datafield>
<datafield tag="209" ind1=" " ind2=" ">
<subfield code="a">经济学</subfield>
<subfield code="b">计算机</subfield>
<subfield code="c">10001</subfield>
<subfield code="d">2005-07-09</subfield>
</datafield>
<datafield tag="610" ind1="0" ind2=" ">
<subfield code="a">计算机</subfield>
<subfield code="a">笔记本</subfield>
</datafield>
</record>
</collection>')
)
select id,name,
extractvalue(x.data,'/collection/record/leader') as A
from xmlexample x;
select extractValue(value(i),'/subfield') xx
from xmlexample x,
table(XMLSequence(extract(x.data,'/collection/record/datafield/subfield'))) i;