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=" ">


    </datafield>
    <datafield tag="209" ind1=" " ind2=" ">


       <subfield code="c">10001</subfield>
       <subfield code="d">2005-07-09</subfield>
    </datafield>
    <datafield tag="610" ind1="0" ind2=" ">


    </datafield>
  </record>
</collection>'::xmltype);

postgres=# select id,name, extract(x.data,'/collection/record/datafield/subfield') as A from xmlexample x;
       id        |    name     |                                                                                                                       
                            a                                                                                                                          

-----------------+-------------+-----------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------
 xxxxxxxxxxxxxxx | my document | 
d code="b">计算机
de="a">笔记本
(1 row)