备注:XML DB是Oracle 9.2中出现的新特性。
1XML DB安装
通过DBCA安装
2XML 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博客 ” ,链接:http://blog.itpub.net/9650775/viewspace-920380/,如需转载,请注明出处,否则将追究法律责任。