XML在Oracle数据库中的应用.docx

1、XML在Oracle数据库中的应用XML在Oracle数据库中的应用2007年10月20日目录第1章 Oracle数据库对XML的支持 31.1. 支持XML的数据库版本 31.2. 什么时候需要XDB数据库属性 31.3. 哪些版本支持XQuery 4第2章 在Oracle中如何存储XML数据 42.1. 以关系型存储XML数据（结构化的存储） 42.2. 以XMLTYPE的方式存储 62.2.1. 非结构化存储 62.2.2. 结构化存储 11Oracle数据库对XML的支持并不是所有的Oracle数据库版本都支持XML的存储以及操作，这里列出了Oracle数据库对XML的支持功能支持XM

2、L的数据库版本Oracle最开始提供XDB性质是在Oracle9.2.0.1这个数据库版本，但是直到9.2.0.3版本才最低限度的提供了XDB托管的级别。查询安装好的数据库是否已经具备了XDB的性质，需要查询Oracle的字典表：DBA_REGISTRY，例如：代码1-1查询当前数据库的安装组件SELECT * FROM DBA_REGISTRY;运行的结果如下：图1-1查询DBA_REGISTRY字典的结果只有在状态列STATUS的数值为VALID的情况下，XDB数据才是安装成功的。什么时候需要XDB数据库属性当在数据库的PROCEDURE、VIEW、FUNCTIONS、COLUMNS或者T

3、ABLES中使用了XMLTYPE这个属性，就会需要XDB数据库的来作支撑。同样如果在数据库中使用了DBMS_XMLDOM、DBMS_XMLQUERY等系统包，也是需要有XDB的支持。哪些版本支持XQueryOracle数据库中支持XQuery标准的最低版本是10.2.0.1。在Oracle中如何存储XML数据在Oracle数据库中存储XML的数据一共有两种方法：数据还是以传统的关系型的存储在数据库中；以XMLTYPE的方式存储在数据库表中；以关系型存储XML数据（结构化的存储）第一种方法是沿用Oracle的传统模式将数据以字符串的类型存储在数据库的varchar列中，调用函数来返回XML类型的

4、数据，例如：代码2-1使用传统关系型的存储方式存储数据/*以HR.EMPLOYEES表格数据为案例*/-对表格数据的扫描结果如下SQL set timing onSQLSELECT first_name|.|last_name FROM HR.EMPLOYEES;FIRST_NAME|.|LAST_NAME-Hermann.BaerShelley.HigginsWilliam.GietzExecuted in 0.08 seconds-使用XMLELEMENT函数来进行转换，选择结果10行以内的数据，运行结果如下SQL SELECT XMLElement(name,first_name|.|l

5、ast_name) FROM hr.employees where rownum10;XMLElement(name,first_name|.|last_name)-Ellen.AbelMozhe.AtkinsonHermann.BaerAmit.BandaSarah.Bell9 rows selectedExecuted in 8.212 seconds在对全表数据扫描时，还出现了如下错误尚未找到合理原因：代码2-2使用Xmlement对表格数据进行全部扫描SQL select count(*) from hr.employees; COUNT(*)- 107Executed in 0.02

6、 secondsSQL SELECT XMLElement(name,first_name|.|last_name) FROM hr.employees;XMLELEMENT(NAME,FIRST_NAME|-Ellen.AbelMozhe.AtkinsonHermann.BaerAmit.BandaSarah.BellSELECT XMLElement(name,first_name|.|last_name) FROM hr.employeesORA-03114: 未连接到 ORALCE结论：优点可以沿用传统的关系型数据库模式来存储，使用B树索引加快查询、修改、删除的速度；便于查询、修改、存

7、储的优化；DML操作技术比较成熟，使用方便、易懂。缺点使用XMLELEMENT转换的方法查询的结果是CLOB类型数据； XML格式的文件需要从关系表中提取数据，重新组织成XML格式的文件，对系统开销比较大，查询速度远远慢于关系型数据库存储。对于多数据量的转换（使用xmlelement转换）会造成数据库会话自动断开的现象。以XMLTYPE的方式存储XML数据通过Oracle新提供的XMLTYPE类型存储，大致分为三类存储模式： 结构化存储 非结构化存储 混合存储一般主要使用前面两种存储方式来存储操作XML类型的数据。结构化存储-以下引自Oracle官方网站。结构化存储。将结构化 XML 存储实现

8、为一组对象。这些对象可通过具有可以在表间实现的引用约束的表以关系格式实现。它们也可以使用一个 XMLSchema 实现，以便将一个 XMLType 文档分解为一组对象。在这里主要是使用了Oracle提供的XMLTYPE这个功能。在使用关系表的情况中，可以因此对这些表进行预先设计，或者使用现有表。该文档实际上通过关系表存储为“虚拟”文档，并保留其逻辑结构。该方法可维护文档对象模型 (DOM) 保真度，但是它不是文档的逐字节物理表示。这可以通过创建一个关于现有关系数据的 XMLType 视图来完成。结构化存储比非结构化存储具有一些性能优势，选择它可通过表和索引设计提供更多的查询和更新优化。结构化存

9、储上的 XML 操作有助于减少内存和存储，因为没有存储 XML 标记，而且存在更细粒度的数据检索和使用。基于 b 树和基于函数的索引使用得到了增强，而且使用 XPath 重写可在文档的部分上执行适当更新。I/O操作速度对比在这里主要是为了测试以结构化存储（使用XMLTYPE的方式）对数据插入的性能，其中测试一以及测试二主要对比将文本数据插入到数据库中表的性能：带XMLTYPE类型字段的表和字符串列类型的表。测试三主要针对以变量形式插入表的性能区别。当前测试使用的机器配置如下： 表2-1当前测试机的物理配置机型操作系统CPU物理内存虚拟内存DELL GS52Windows 2000 Profes

10、sionIntel(R)4 2.80HZ1G2.2G当前数据库的SGA以及temporary的大小如下：-SGA大小SQL select * from v$sga;NAME VALUE- -Fixed Size 1248600Variable Size 96469672Database Buffers 188743680Redo Buffers 2945024Executed in 0.01 seconds-查看临时表空间大小SQL select tablespace_size from dba_tablespace_usage_metrics where tablespace_name=TE

11、MP;TABLESPACE_SIZE- 4194302Executed in 0.01 seconds测试一：demo.xml文件大小为32k，文本文件行数为553行对文本文件插入操作性能比较，测试数据库用户=scott；测试文件=d:xmldemo.xml 内容为标准的XML格式：代码2-1XML文件内容： - - - DEMONSTRATIVO_ANALISE_CONTA 1 2007-03-26 16:33:46Z - 123456 - 654321 2.01.01 操作系统物理大小：32kb测试步骤如下：（1） 用sys用户创建目录，将该目录上的read、write权限赋予scott用

12、户代码2-2创建两张测试表格：C:sqlplus “/as sysdba”sqlcreate directory xmldir as d:/xml/;目录已创建。sqlgrant read,write on directory xmldir;授权成功。（2） 创建两张表格，一张包含XMLTYPE类型，一张为字符串类型，代码如1-3所示：代码2-3使用scott用户创建两张表格：sqlconn scott/tigerdemosqlcreate table xml_demo( id number, xml_information xmltype);表已创建成功。Sql create table x

13、ml_information( id number, information varchar2(2000)；表格创建成功。（3） 插入字符串列的表格测试结果：代码2-4将数据插入到xml_information测试declare 2 file_type utl_file.file_type; 3 file_info varchar2(2000); 4 begin_time date; 5 end_time date; 6 7 time_diff number; -计算插入数据消耗的时间 8 begin 9 select sysdate into begin_time from dual; 10

14、 11 file_type:=utl_file.fopen(XMLDIR,demo1.xml,R); 12 for i in 1.553loop 13 utl_file.get_line(file_type, file_info ); 14 insert into xml_information 15 values(i,file_info); 16 17 end loop; 18 select sysdate into end_time from dual; 19 select (end_time-begin_time)*24*60*60 into p from dual; 20 dbms_o

15、utput.put_line(to_char(begin_time,yyyy-mm-dd hh24:mi:ss); 21 dbms_output.put_line(to_char(end_time,yyyy-mm-dd hh24:mi:ss); 22 23 dbms_output.put_line(插入数据消耗时间：|time_diff); 24 end; 25 /2007-10-21 17:20:302007-10-21 17:20:30插入数据消耗时间：0（4） 将数据插入带XMLTYPE表格测试结果：代码2-5将数据插入带XMLTYPE表格测试结果declare 2 begin_time

16、 date; 3 end_time date; 4 time_diff number; 5 begin 6 select sysdate into begin_time from dual; 7 8 insert into xml_demo 9 values(1,xmltype(bfilename(XMLDIR,demo1.xml),nls_charset_id(AL32UTF8); 10 11 select sysdate into end_time from dual; 12 select (end_time-begin_time)*24*60*60 into p from dual; 1

17、3 dbms_output.put_line(to_char(begin_time,yyyy-mm-dd hh24:mi:ss); 14 dbms_output.put_line(to_char(end_time,yyyy-mm-dd hh24:mi:ss); 15 16 dbms_output.put_line(插入数据消耗时间：|time_diff); 17 18 end; 19 /2007-10-21 17:22:322007-10-21 17:22:32插入数据消耗时间：0测试结论：当demo.xml的大小为32kb的时候，将文本文件的数据插入到字符串类型的表格和插入到带XMLTYPE

18、类型的表格的消耗是可忽略的。测试二：demo1.xml文件大小为2834k，文本文件行数为48428行为保证测试的准确性，在测试之前将shared_pool中的数据清空，测试步骤如下：（1） 将demo1.xml数据插入到字符列的xml_information表格中代码2-6将2834k数据插入到字符串类型的表格中sqlalter system flush shared_poolSystem altereddeclare 2 file_type utl_file.file_type; 3 file_info varchar2(2000); 4 begin_time date; 5 end_ti

19、me date; 6 7 time_diff number; 8 begin 9 select sysdate into begin_time from dual; 10 11 file_type:=utl_file.fopen(XMLDIR,demo1.xml,R); 12 for i in 1.48428 loop 13 utl_file.get_line(file_type, file_info ); 14 insert into xml_information 15 values(i,file_info); 16 17 end loop; 18 select sysdate into

20、end_time from dual; 19 select (end_time-begin_time)*24*60*60 into p from dual; 20 dbms_output.put_line(to_char(begin_time,yyyy-mm-dd hh24:mi:ss); 21 dbms_output.put_line(to_char(end_time,yyyy-mm-dd hh24:mi:ss); 22 23 dbms_output.put_line(插入数据消耗时间：|time_diff); 24 end; 25 /2007-10-21 17:55:132007-10-2

21、1 17:55:17插入数据消耗时间为：4（2） 将demo1.xml数据插入到带XMLTYPE类型表格xml_demo中代码2-7将2834k数据插入到xml_demo表格中SQL alter system flush shared_pool;System alteredSQL declare 2 begin_time date; 3 end_time date; 4 time_diff number; 5 begin 6 select sysdate into begin_time from dual; 7 8 insert into xml_demo 9 values(1,xmltype

22、(bfilename(XMLDIR,demo1.xml),nls_charset_id(AL32UTF8); 10 11 select sysdate into end_time from dual; 12 select (end_time-begin_time)*24*60*60 into p from dual; 13 dbms_output.put_line(to_char(begin_time,yyyy-mm-dd hh24:mi:ss); 14 dbms_output.put_line(to_char(end_time,yyyy-mm-dd hh24:mi:ss); 15 16 db

23、ms_output.put_line(插入数据消耗时间：|time_diff); 17 18 end; 19 /2007-10-21 17:56:012007-10-21 17:56:02插入数据消耗时间：1PL/SQL procedure successfully completed测试结论：文本文件的数据达到2M时，插入到XMLTYPE类型表中的数据时间只有插入字符串类型的表格时间的1/4。测试三：以变量形式存储选取一段XML数据保存在变量中，以变量的形式插入数据库，这部分测试主要针对将变量数据插入到表的性能：带XMLTYPE列的表和字符串数据类型的表。详见代码2-8：代码2-8数据以变量

24、插入到带XMLTYPE类型列的表格中-创建带xmltype列的表格xml_demo(id number ,xml_information xmltype)SQLcreate table xml_demo (id number,xml_information);Table createdSQL set timing on SQLalter system flush shared_pool;SQL declare xml_sql varchar2(4000); 2 begin 3 xml_sql:= 4 1973 5 FOODWORLD 6 MERCHANT 7 foodworld 8 9 18

25、SALISBURY ARCADE 10 TACOMA 150 GROCERY MERCHANT151 ;152 153 dbms_output.put_line(字符串长度:|length(xml_sql);154 insert into xml_demo155 values(1,xmltype(xml_sql);156 157 end;158 /字符串长度:3731PL/SQL procedure successfully completedExecuted in 0.231 secondsSQL下面是用字符串数据类型进行测试：代码2-9数据以变量形式插入到字符串类型列的表格中-创建字符串类型的表格SQL create table test( information varchar2(4000);Table createdSQL alter system flush shared_pool;System alteredSQL declare xml_sql varchar2(4000); 2 begin 3 xml_sql:= 4 1973 5 FOODWORLD 6 MERCHANT 7 foodworld 8 149