备注：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:oracleXMLDB';

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('

6

7 13111111111

8

9

10 13999999999

11

12 '

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')

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

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

shanxi806

shanxi806

100

1000

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

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')

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

String

String

int

String

String

float

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9650775/viewspace-920380/ ，如需转载，请注明出处，否则将追究法律责任。