初识Oracle的XMLType

180次阅读

共计 8124 个字符，预计需要花费 21 分钟才能阅读完成。

Oracle xmltype 是从 Oracle 9i 开始支持一种新的数据类型，用于存储和管理 xml 数据，并提供了很多的 functions，用来保存、检索和操作 xml 文档和管理节点。XMLType 是系统定义的类型，所以可以使用它作为一个函数的参数或表或视图中的列的数据类型。也可以创建表和视图的 XMLType。当你创建一个表中的一个 XMLType 列，你可以选择 XML 数据存储在一个 CLOB 列，作为二进制 XML（内部存储为 CLOB），或对象的关系。

下面将介绍 Oracle XMLType 的一些基本使用。

1、创建一个包含 XMLType 类型列的表，并插入测试数据

zx@TEST>create table t1 (id number,xml_data sys.xmltype);

Table created.

zx@TEST>desc t1
Name Null? Type
—————————————————————————————————– ——– ——————————————————————–
ID NUMBER
XML_DATA SYS.XMLTYPE

zx@TEST>insert into t1 values(1,’abc’);
insert into t1 values(1,’abc’)
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected ‘<‘ instead of ‘a’
Error at line 1

zx@TEST>insert into t1 values(1,'<abc>1</abc>’);

1 row created.

zx@TEST>col xml_data for a80
zx@TEST>select * from t1;

ID XML_DATA
———- ——————————————————————————–
1 <abc>1</abc>

从上面看出，XMLType 可以做为列中列的数据类型，在插入数据时必须符合 XML 格式才能插入，否则会报错。

2、查看 XMLType 的存储形式

从 user_segments 视图中看出 XMLType 列是以 LOB 字段存储的

zx@TEST>select segment_name,segment_type from user_segments;

SEGMENT_NAME SEGMENT_TYPE
—————————— ——————————————————
T1 TABLE
SYS_IL0000074607C00003$$ LOBINDEX
SYS_LOB0000074607C00003$$ LOBSEGMENT

查看 user_lobs 是否对应 xml_data 列

zx@TEST>col column_name for a30
zx@TEST>col table_name for a30
zx@TEST>select table_name,column_name,segment_name from user_lobs;

TABLE_NAME COLUMN_NAME SEGMENT_NAME
—————————— —————————— ——————————
T1 SYS_NC00003$ SYS_LOB0000074607C00003$$

从上面的查询结果可以看到 LOBSEGMENT 对应的表 T1 中的列 SYS_NC00003$，而不是 XML_DATA 列，而且表 T1 中没有这个列，再次查询 user_tab_cols 视图

zx@TEST>col data_type for a30
zx@TEST>select TABLE_NAME,COLUMN_NAME,DATA_TYPE,HIDDEN_COLUMN,COLUMN_ID from user_tab_cols;

TABLE_NAME COLUMN_NAME DATA_TYPE HIDDEN_CO COLUMN_ID
—————————— —————————— —————————— ——— ———-
T1 ID NUMBER NO 1
T1 XML_DATA XMLTYPE NO 2
T1 SYS_NC00003$ CLOB YES 2

从上面的查询中可以看出列 SYS_NC00003$ 是表 T1 中的隐藏列，它与列 XML_DATA 列的 COLUMN_ID 都是 2，说明它们是同一列。由此可以看出 XMLType 类型的数据由 CLOB 类型列协助保存。由下面的表定义也可以推断出这一点：

zx@TEST>select dbms_metadata.get_ddl(‘TABLE’,’T1′,USER) from dual;

DBMS_METADATA.GET_DDL(‘TABLE’,’T1′,USER)
——————————————————————————–

CREATE TABLE “ZX”.”T1″
(“ID” NUMBER,
“XML_DATA” “SYS”.”XMLTYPE”
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENT
S 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS”
XMLTYPE COLUMN “XML_DATA” STORE AS BASICFILE CLOB (
TABLESPACE “USERS” ENABLE STORAGE IN ROW CHUNK 8192 PCTVER
SION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAUL
T))

3、关于 XML 的一些函数

1) sys.xmltype.createxml 函数

使用 sys.xmltype.createxml 创建 XMLType 类型的数据

1zx@TEST>insert into t1 values(2,
2 sys.xmltype.createxml(‘<?xml version=”1.0″ encoding=”UTF-8″ ?>
3 <collection xmlns=””>
4 <record>
5 <leader>—–nam0-22—–^^^450-</leader>
6 <datafield tag=”200″ ind1=”1″ ind2=” “>
7 <subfield code=”a”> 抗震救灾 </subfield>
8 <subfield code=”f”> 奥运会 </subfield>
9 </datafield>
10 <datafield tag=”209″ ind1=” ” ind2=” “>
11 <subfield code=”a”> 经济学 </subfield>
12 <subfield code=”b”> 计算机 </subfield>
13 <subfield code=”c”>10001</subfield>
14 <subfield code=”d”>2005-07-09</subfield>
15 </datafield>
16 <datafield tag=”610″ ind1=”0″ ind2=” “>
17 <subfield code=”a”> 计算机 </subfield>
18 <subfield code=”a”> 笔记本 </subfield>
19 </datafield>
20 </record>
21 </collection>’));

1 row created.
zx@TEST>commit;

Commit complete.

zx@TEST>col xml_data for a80
zx@TEST>select * from t1;

ID XML_DATA
———- ——————————————————————————–
2 <?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) extractvalue 函数

extractvalue 函数提供对 XML 文件的检索功能只能返回一个节点的一个值，如果该节点有多个值，则系统提示错误。

zx@TEST>col data for a80
zx@TEST>select extractvalue(i.xml_data,’/collection/record/leader’) data from t1 i;

DATA
——————————————————————————–
—–nam0-22—–^^^450-

zx@TEST>select extractvalue(i.xml_data,’/collection/record/datafield’) data from t1 i;
select extractvalue(i.xml_data,’/collection/record/datafield’) data from t1 i
*
ERROR at line 1:
ORA-19025: EXTRACTVALUE returns value of only one node

3) extract 函数

extract 函数查询 XMLType 的内容，它可以返回一个节点下的所有值。它返回的是 XML 格式的。

zx@TEST>select extract(i.xml_data,’/collection/record/datafield/subfield’) data from t1 i;

DATA
——————————————————————————–
<subfield xmlns=”” code=”a”> 抗震救灾 </subfield><subfield xmlns=”” code=”f”> 奥运
会 </subfield><subfield xmlns=”” code=”a”> 经济学 </subfield><subfield xmlns=”” cod
e=”b”> 计算机 </subfield><subfield xmlns=”” code=”c”>10001</subfield><subfield xml
ns=”” code=”d”>2005-07-09</subfield><subfield xmlns=”” code=”a”> 计算机 </subfield
><subfield xmlns=”” code=”a”> 笔记本 </subfield>

查询 tag=”610″，且 code=”a” 所对应的值

zx@TEST>select extract(i.xml_data,’/collection/record/datafield[@tag=”610″]/subfield[@code=”a”]’) data from t1 i;

DATA
——————————————————————————–
<subfield xmlns=”” code=”a”> 计算机 </subfield><subfield xmlns=”” code=”a”> 笔记本 <
/subfield>

4) table 和 XMLSequence

如果只想返回它值就要是用上面的两个函数了。

zx@TEST>select extractvalue(value(i),’/subfield’) data
2 from t1 x,
3 table(xmlsequence(extract(x.xml_data,’/collection/record/datafield[@tag=”610″]/subfield[@code=”a”]’))) i;

DATA
——————————————————————————–
计算机
笔记本

4) updatexml

使用 updatexml 更新 XMLType 里的内容，把 tag=”209″、code=”a” 的经济学修改为“赵旭”

zx@TEST>update t1 set xml_data=
2 updatexml(xml_data,’/collection/record/datafield[@tag=”209″]/subfield[@code=”a”]/text()’,’ 赵旭 ’);

1 row updated.

zx@TEST>select * from t1;

ID XML_DATA
———- ——————————————————————————–
2 <?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>