extract
extract(xmltype类型,节点)。
create table xmlexample(ID varchar(100),name varchar(20),data xmltype);
insert into xmlexample(id,name,data) values('xxxxxxxxxxxxxxx','my document','<?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>'::xmltype);
teledb=# select id,name, extract(x.data,'/collection/record/datafield/subfield') as A from xmlexample x;
id | name |
a
-----------------+-------------+-----------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------
xxxxxxxxxxxxxxx | my document | <subfield code="a">抗震救灾</subfield><subfield code="f">奥运会</subfield><subfield code="a">经济学</subfield><subfiel
d code="b">计算机</subfield><subfield code="c">10001</subfield><subfield code="d">2005-07-09</subfield><subfield code="a">计算机</subfield><subfield co
de="a">笔记本</subfield>
(1 row)
