Oracle XML DB之浅入浅出

http://www.hellodba.com/Doc/oracle_xml_db.htm

 

 

备注：XML DB是Oracle 9.2中出现的新特性。

1         XML DB安装

通过DBCA安装

 

2         XML DB数据处理

2.1    存储XML数据到XML表中

2.1.1创建一个有XMLType类型字段的表：

create table xmlcontent (keyvalue varchar2(10) primary key,

                                       xmlvalue xmltype);

 

2.1.2创建一个XMLType类型表

create table xmltable of xmltype;

 

2.1.3从XML文件中读取数据存储到XML表中

创建Directory指向存放XML文件的路径：

SQL> grant create any directory to xdb;

Grant succeeded.

SQL> conn xdb/xdb

Connected.

SQL> create directory XMLDIR as 'C:\oracle\XMLDB';

Directory created.

 

创建存储过程，从XML文件中读取数据存储到XML表中

create or replace function getClobDocument(

   filename in varchar2,

   charset in varchar2 default NULL)

 return CLOB deterministic

 is

    file            bfile := bfilename(‘XMLDIR’,filename);

    charContent     CLOB := ' ';

    targetFile      bfile;

    lang_ctx        number := DBMS_LOB.default_lang_ctx;

    charset_id      number := 0;

    src_offset      number := 1 ;

    dst_offset      number := 1 ;

    warning         number;

 begin

   if charset is not null then

       charset_id := NLS_CHARSET_ID(charset);

   end if;

   targetFile := file;

   DBMS_LOB.fileopen(targetFile, DBMS_LOB.file_readonly);

   DBMS_LOB.LOADCLOBFROMFILE(charContent, targetFile,

           DBMS_LOB.getLength(targetFile), src_offset, dst_offset,

           charset_id, lang_ctx,warning);

   DBMS_LOB.fileclose(targetFile);

   return charContent;

 end;

 /

-- you can use getCLOBDocument() to generate a CLOB from a file containin

-- an XML document. For example, the following statement inserts a row into the

-- XMLType table Example2 created earlier:

 

向表中插入数据：

SQL> INSERT INTO XMLTABLE

  2  VALUES(XMLTYPE(getCLOBDocument('init.xml')));

1 row created.

SQL> INSERT INTO XMLContent

  2  VALUES(1, XMLTYPE(getCLOBDocument('init.xml', 'UTF8')));

1 row created.

SQL> commit;

Commit complete.

 

2.2    更新XML表数据

2.2.1普通Update方式

 

SQL> UPDATE XMLTABLE X SET VALUE(X)=XMLTYPE(getCLOBDocument('init.xml'));

1 row updated.

 

SQL> UPDATE XMLContent SET xmlvalue=XMLTYPE(getCLOBDocument('init.xml'))

  2  WHERE keyvalue='1';

1 row updated.

 

SQL> commit;

Commit complete.

 

2.2.2使用updatexml()更新节点值

SQL> UPDATE xmlcontent

  2      SET xmlvalue = updateXML(xmlvalue,

  3                             '/sqlstress/config/userName/text()',

  4                             'shanxi')

  5      WHERE existsNode(xmlvalue,

  6         '/sqlstress/config[userName="shanxi806"]') = 1;

1 row updated.

 

SQL> commit;

Commit complete.

 

SQL> select extractvalue(xmlvalue,'/sqlstress/config/userName')

  2  from xmlcontent;

EXTRACTVALUE(XMLVALUE,'/SQLSTRESS/CONFIG/USERNAME')

---------------------------------------------------------------------

shanxi

 

2.2.3使用updatexml()更新一个节点树

SQL> UPDATE xmlcontent

  2    SET xmlvalue =

  3    updateXML(xmlvalue,

  4              '/sqlstress/functions/function[1]/parameters/prameter[2]/valuecope',

  5              xmltype('<valueScope rangeType="RANGE" dataType="int">

  6                          <minValue>

  7                              13111111111

  8                          </minValue>

  9                          <maxValue>

 10                              13999999999

 11                          </maxValue>

 12                      </valueScope>'

 13               )

 14    )

 15    WHERE existsNode(xmlvalue,

 16          '/sqlstress/functions/function[1]/parameters/parameter[@pid=2]'

 17    ) = 1;

 

1 row updated.

2.3    从XML表中读取数据

主要利用extract(), extractValue(), and existsNode()等几个函数。”Init.xml”的内容参见附录。

2.3.1existsNode ()

EXISTSNODE函数检查XML中的某一个节点是否存在。如果存在，返回1，否则返回0。

SQL> SELECT existsNode(value(X),'/sqlstress/config/connURL')

  2     FROM XMLTABLE X;

EXISTSNODE(VALUE(X),'/SQLSTRESS/CONFIG/CONNURL')

------------------------------------------------

                                               1

 

SQL> SELECT existsNode(value(X),'/sqlstress/config/connURLFalse')

  2     FROM XMLTABLE X;

EXISTSNODE(VALUE(X),'/SQLSTRESS/CONFIG/CONNURLFALSE')

-----------------------------------------------------

                                                    0

 

SQL> SELECT existsNode(xmlvalue,'/sqlstress/config/connURL')

  2    FROM XMLContent X

  3   WHERE keyvalue = '1';

EXISTSNODE(XMLVALUE,'/SQLSTRESS/CONFIG/CONNURL')

------------------------------------------------

                                               1

 

SQL> SELECT existsNode(value(X),

  2         '/sqlstress/config[userPWD="'||chr(10)||'            shanxi806'||chr(10)||'        "'||']')

  3     FROM XMLTABLE X;

EXISTSNODE(VALUE(X),'/SQLSTRESS/CONFIG[USERPWD="'||CHR(10)||'SHANXI806'||CHR(10)

--------------------------------------------------------------------------------

                                                                               1

 

SQL> SELECT count(*)

  2    FROM XMLContent x

  3   WHERE existsNode(xmlvalue,'/sqlstress/config[userName="shanxi806"]') = 1;

  COUNT(*)

----------

         1

 

SQL> SELECT count(*)

  2    FROM XMLContent x

  3   WHERE existsNode(xmlvalue,'/sqlstress/functions/function[@id=1]') = 1;

  COUNT(*)

----------

         1

 

SQL> SELECT count(*)

  2    FROM XMLContent x

  3   WHERE existsNode(xmlvalue,'/sqlstress/functions/function/parameters/parameter[2][@pid=2]') = 1;

  COUNT(*)

----------

         1

 

2.3.2extractValue()

EXTRACTVALUE()是从某个节点中读取值

 

SQL> select extractValue(xmlvalue, '/sqlstress/config/userPWD')

  2  from xmlcontent

  3  where keyvalue='1';

 

EXTRACTVALUE(XMLVALUE,'/SQLSTRESS/CONFIG/USERPWD')

----------------------------------------------------------------

            shanxi806

 

SQL> SELECT extractvalue(xmlvalue, '/sqlstress/functions/function[1]/parameters/parameter[2]/@pid')

  2    FROM xmlcontent

  3   WHERE keyvalue = '1';

EXTRACTVALUE(XMLVALUE,'/SQLSTRESS/FUNCTIONS/FUNCTION[1]/PARAMETERS/PARAMETER[2]/

--------------------------------------------------------------------------------

2

 

ExtractValue只能返回一个确切的位置节点的值，如果存在多个相同节点，Oracle就会报错：

SQL> SELECT extractvalue(xmlvalue, '/sqlstress/functions/function[1]/parameters/

parameter')

  2    FROM xmlcontent

  3   WHERE keyvalue = '1';

  FROM xmlcontent

       *

ERROR at line 2:

ORA-19025: EXTRACTVALUE returns value of only one node

 

SQL> SELECT extractvalue(xmlvalue, '/sqlstress/config')

  2    FROM xmlcontent

  3   WHERE keyvalue = '1';

  FROM xmlcontent

       *

ERROR at line 2:

ORA-19025: EXTRACTVALUE returns value of only one node

 

2.3.3Extract()

EXTRACT函数返回一个XML文档的一个节点树，或者某一节点下所有符合条件的节点。

返回一个节点树：

SQL> set line 100

SQL> set lone 20000

SQL> SELECT extract(xmlvalue, '/sqlstress/config')

  2    FROM xmlcontent

  3   WHERE keyvalue = '1';

 

EXTRACT(XMLVALUE,'/SQLSTRESS/CONFIG')

-----------------------------------------------------

<config>

  <connURL>

            jdbc:oracle:thin:@10.71.111.231:1521:P51

        </connURL>

  <userName>shanxi806</userName>

  <userPWD>

            shanxi806

        </userPWD>

  <threadNum>

            100

        </threadNum>

  <execNumPerThread>

            1000

        </execNumPerThread>

</config>

 

返回所有符合条件的节点：

SQL> SELECT extract(xmlvalue, '/sqlstress/functions/function[1]/parameters/parameter/paraType')

  2    FROM xmlcontent

  3   WHERE keyvalue = '1';

 

EXTRACT(XMLVALUE,'/SQLSTRESS/FUNCTIONS/FUNCTION[1]/PARAMETERS/PARAMETER/PARATYPE')

--------------------------------------------------------------------------------

<paraType>

                        String

                    </paraType>

<paraType>

                        String

                    </paraType>

<paraType>

                        int

                    </paraType>

<paraType>

                        String

                    </paraType>

<paraType>

                        String

                    </paraType>

<paraType>

                        float

font-