Oracle中对XMLType的简单操作（extract、extractvalue）

最新推荐文章于 2024-09-25 16:49:25 发布

ahngzw77

最新推荐文章于 2024-09-25 16:49:25 发布

阅读量1.7k

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

41 篇文章 1 订阅

订阅专栏

本文详细介绍了如何在 Oracle 数据库中对 XMLType 字段进行操作，包括创建 XML 文件、在表中存储 XML 数据以及使用 extractvalue 和 extract 函数检索 XML 内容。通过实例演示了如何提取特定节点和子节点的值。

摘要由CSDN通过智能技术生成

前几天一直在做Oracle对XMLType字段的操作，我还不是Oracle大拿，到网上找了很多资料，但是很多就是单一功能的介绍，不能很好的解决问题，现在在这里总结下。

1、下面先创建一个名未test.xml的配置文件。

<?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>

2、在Oracle中创建一个存放该XML文件的表：

create or replace table xmlexample(
ID varchar2(10),
name varchar2(20),
data xmltype
)
insert into xmlexample(id,name,data)
values(sys_guid(),'my document',
xmltype
(
bfilename(filepath,filename),
nls_charset_id('AL32UTF8')
)
)

其中filepath为文件路径，filename为文件名。

3、extractvalue()函数的使用
Oracle提供对XML文件的检索功能（extractvalue），extractvalue只能返回一个节点的一个值，具体操作方法如下：

SQL> select id,name,
2 extractvalue(x.data,'/collection/record/leader') as A
3 from xmlexample x;

ID NAME A
---------- -------------------- ----------------------------------
1 my document -----nam0-22-----^^^450-

如果该节点有两个值，则系统提示错误。

4、extract()函数的使用
如果想查询所有subfield的值就要用到extract()，它可以返回一个节点下的所有值。操作如下

SQL> select id,name,
2 extract(x.data,'/collection/record/datafield/subfield') as A
3 from xmlexample x;

ID NAME A
---------- -------------------- ---------------------------------------------------------
1 my document <subfield xmlns="" code="a">抗震救灾</subfield><subfield xmlns="" code="f">何观.....

可以看到它返回的是XML格式的。如果我们想只返回它值就要是用两个函数了。

5、table和XMLSequence
操作如下：

SQL> select extractValue(value(i),'/subfield') xx
2 from xmlexample x,
3 table(XMLSequence(extract(x.data,'/collection/record/datafield/subfield'))) i;

XX
--------------------------------------------------------------------------------
抗震救灾
何观华
经济学
计算机
10001
2005-07-09
计算几哈哈

7 rows selected。

6、检索出特定的节点的特定值
有时候我们在实际操作的时候并不是检索出所有值，而是根据条件查询出我们所需要的信息。如果我们想检索出
<datafield tag="209" ind1=" " ind2=" ">
<subfield code="a">经济学</subfield>
中的值-经济学

操作如下：

SQL> select id,name,
2 extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A
3 from xmlexample x;

ID NAME A
---------- -------------------- ---------------------------
1 my document 经济学

7、总结

Oracle对与XMLType的操作有很多种，还要靠大家自己去发现。数据库对XML的检索就是吧XML的节点当作一个列来检索，而不同的是表里装的是二维的数据，而XML中可以装N维。还有就是，表中列不存在就会提示无效标识符，如果节点不存在，则检索出NULL，不会报错。所以，对与XML文件的操作通常是通过试图来完成。

--------------------------------------------------------------------------分割线-------------------------------------------------------------------------------------------------

为了让大家能更快的解决类似这样的问题，所有提供小实例，供大家参考。

有这样一个表，表名：AA
字段有： id        number，
        c_cml     clob

其中，clob里面存储的为xml内容

其中xml里有字段：<PurchaseOrder>
                        <DataElement name="member_id">90558976001</DataElement>
                        <DataElement name="hicn">509488308A</DataElement>
                        <DataElement name="EID">285320</DataElement>
                 </PurchaseOrder>

问题：从AA表中select出xml中的id,hicn和member_id字段

解答：
1：创建表
create table AA(id number,c_cml clob);

2：插入字段
insert into aa values(1,to_clob('<PurchaseOrder>
                                   <DataElement name="member_id">90558976001</DataElement>
                                   <DataElement name="hicn">509488308A</DataElement>
                                   <DataElement name="EID">285320</DataElement>
                                 </PurchaseOrder>'));

3:查询
select extractvalue(xmltype(c_cml),'/PurchaseOrder/DataElement[@name="member_id"]'),
       extractvalue(xmltype(c_cml),'/PurchaseOrder/DataElement[@name="hicn"]'),
    id
from aa;