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