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
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
IDNUMBER
XML_DATASYS.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 "
Error at line 1
zx@TEST>insert into t1 values(1,"1");
1 row created.
zx@TEST>col xml_data for a80
zx@TEST>select * from t1;
ID XML_DATA
---------- --------------------------------------------------------------------------------
1 1
从上面看出,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_COCOLUMN_ID
------------------------------ ------------------------------ ------------------------------ --------- ----------
T1 ID NUMBER NO 1
T1 XML_DATA XMLTYPE NO 2
T1 SYS_NC00003$ CLOB YES2
从上面的查询中可以看出列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类型的数据zx@TEST>insert into t1 values(2,
2 sys.xmltype.createxml("<?xml version="1.0" encoding="UTF-8" ?>
3
4
5 -----nam0-22-----^^^450-
6
7 抗震救灾
8 奥运会
9
10
11 经济学
12 计算机
13 10001
14 2005-07-09
15
16
17 计算机
18 笔记本
19
20
21 "));
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"?>
-----nam0-22-----^^^450-
抗震救灾
奥运会
经济学
计算机
10001
2005-07-09
计算机
笔记本
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
--------------------------------------------------------------------------------
抗震救灾奥运
会经济学
e="b">计算机
10001ns="" code="d">2005-07-09
计算机>笔记本
查询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>
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"?>
-----nam0-22-----^^^450-
抗震救灾
奥运会
赵旭
计算机
10001
2005-07-09
计算机
笔记本
官方文档: