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)