初识Oracle的XMLType

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>

参考：

官方文档：

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