Oracle xmltype是从Oracle 9i开始支持一种新的数据类型，用于存储和管理xml数据，并提供了很多的functions，用来保存、检索和操作xml文档和管理节点。XMLType是系统定义的类型，所以可以使用它作为一个函数的参数或表或视图中的列的数据类型。也可以创建表和视图的XMLType。当你创建一个表中的一个XMLType列，你可以选择XML数据存储在一个CLOB列，作为二进制XML（内部存储为CLOB），或对象的关系。
下面将介绍Oracle XMLType的一些基本使用。
1、创建一个包含XMLType类型列的表，并插入测试数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
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字段存储的
|
1
2
3
4
5
6
7
|
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列
|
1
2
3
4
5
6
7
|
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视图
|
1
2
3
4
5
6
7
8
|
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类型列协助保存。由下面的表定义也可以推断出这一点：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
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类型的数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
zx@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文件的检索功能只能返回一个节点的一个值，如果该节点有多个值，则系统提示错误。
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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格式的。
|
1
2
3
4
5
6
7
8
9
|
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"所对应的值
|
1
2
3
4
5
6
|
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
如果只想返回它值就要是用上面的两个函数了。
|
1
2
3
4
5
6
7
8
|
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"的经济学修改为“赵旭”
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
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>
|
参考：http://blog.csdn.net/r_youxia_dayu/article/details/6686106
http://database.51cto.com/art/200911/163928.htm
http://blog.itpub.net/17203031/viewspace-708738/
官方文档：
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/t_xml.htm#ARPLS369
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions225.htm#SQLRF06172
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions060.htm#SQLRF00640
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions061.htm#SQLRF06173
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1905333，如需转载请自行联系原作者
