Oracle XQuery查询构建和转换XML.docx

Oracle XQuery查询构建和转换XML.docx

《Oracle XQuery查询构建和转换XML.docx》由会员分享，可在线阅读，更多相关《Oracle XQuery查询构建和转换XML.docx（8页珍藏版）》请在冰豆网上搜索。

OracleXQuery查询构建和转换XML

在Oracle数据库10g第2版中，Oracle引入了一个与该数据库集成的全功能自带XQuery引擎，该引擎可用于完成与开发支持XML的应用程序相关的各种任务。

XQuery是一种用于处理XML数据模型的查询语言，它实际上可操作任何类型的可用XML表达的数据。

尽管OracleXQuery实施使您可以使用数据库数据和外部数据源，但在处理数据库中存储的结构化数据方面，OracleXMLDB通常可以显著提高性能。

　　本文提供的示例不仅演示了在什么场合下以及如何使用XQuery查询、构建和转换XML，而且还演示了如何监控和分析XQuery表达式的性能执行，从而找到更高效的方法来处理同一工作负载。

　　基于关系数据构建XML

　　在需要的情况下（例如，向Web服务发送结果），您可能要基于关系数据构建XML。

要在Oracle数据库10g第2版之前的版本中完成此任务，通常需要使用SQL/XML生成函数，如XMLElement、XMLForest和XMLAgg（）。

在Oracle数据库10g第2版中，XQuery将比这些函数更为高效。

具体而言，在XQuery表达式内部使用ora:

viewXQuery函数，您可以查询现有的关系表或视图以及即时构建XML，从而不必通过关系数据显式创建XML视图。

列表1中的PL/SQL代码演示了如何使用ora:

view基于示例数据库模式HR的默认员工关系表中存储的数据构建XML文档。

　　列表1：

使用ora:

view基于关系数据创建XML

　　BEGINIF（DBMS_XDB.CREATEFOLDER（'/public/employees'））THENDBMS_OUTPUT.PUT_LINE（'Folderiscreated'）;ELSEDBMS_OUTPUT.PUT_LINE（'Cannotcreatefolder'）;ENDIF;COMMIT;END;/

　　DECLAREXMLdocXMLType;BEGINSELECTXMLQuery（'for$jin1return（{for$iinora:

view（"HR","employees"）/ROWwhere$i/EMPLOYEE_ID<=102return（{xs:

string（$i/EMPLOYEE_ID）}{xs:

string（$i/LAST_NAME）}{xs:

integer（$i/SALARY）}）}）'RETURNINGCONTENT）INTOXMLdocFROMDUAL;IF（DBMS_XDB.CREATERESOURCE（'/public/employees/employees.xml',XMLdoc））THENDBMS_OUTPUT.PUT_LINE（'Resourceiscreated'）;ELSEDBMS_OUTPUT.PUT_LINE（'Cannotcreateresource'）;ENDIF;COMMIT;END;/

　　在列表1中的第一个PL/SQL过程中，您只是在XML信息库中创建了一个新文件夹。

在该信息库文件夹中，您随后将存储此处显示的第二个PL/SQL过程中创建的XML文档。

第二个PL/SQL过程首先发出SELECT语句，该语句使用XMLQuerySQL函数基于关系数据构建XML。

对于XQuery表达式（XMLQuery在此处将其用作参数）而言，请注意嵌套的FLWOR表达式中使用的ora:

viewXQuery函数。

在该示例中，ora:

view获取两个输入参数，即“HR”和“employees”，它们指示该函数查询属于HR数据库模式的员工表。

因此，ora:

view将返回一个表示HR.employees表行的员工XML文档序列。

但为了节省结果文档中的空间，只将前三个员工记录传递给结果序列。

这是通过在FLWOR表达式的where子句中指定$i/EMPLOYEE_ID<=102而实现的。

请注意FLWOR表达式的return子句中使用的xs:

string（）和xs:

integer（）XQuery类型表达式。

实际上，此处使用的这两个XQuery表达式不仅将XML节点值转换为相应的类型，而且还将提取这些节点值。

随后，生成的员工XML文档作为employees.xml保存到之前在列表1中另一个PL/SQL过程中创建的/public/employeesXML信息库文件夹。

要确保此操作已完成，可执行以下查询：

　　SELECTXMLQuery（'for$iinfn:

doc（"/public/employees/employees.xml"）return;$i'RETURNINGCONTENT）ASRESULTFROMDUAL;

　　该查询应生成以下输出：

　　100King24000101Kochhar17000102DeHaan17000

　　在以上XQuery中，fn:

docXQuery函数用于访问OracleXMLDB信息库中存储的单个XML文档。

但如果要处理一些具有相同或相似结构的XML文档（存储在同一XML信息库文件夹中），应该怎么做?

这种情况下，另一个用于处理XML信息库资源的XQuery函数（即fn:

collection）可能会派上用场。

本文稍后将介绍几个有关如何使用fn:

collectionXQuery函数的示例。

　　查询XMLType数据

　　XQuery使您可以操作基于XML模式以及非基于模式的数据。

以下示例演示了如何使用XMLTable函数从OE演示数据库模式中查询基于PurchaseOrderXML模式的XMLType表。

　　SELECTttab.COLUMN_VALUEASOrderTotalFROMpurchaseorder,XMLTable（'for$iin/PurchaseOrderwhere$i/User="EABEL"return;{$i/Reference}{fn:

sum（for$jin$i/LineItems/LineItem/Partreturn（$j/@Quantity*$j/@UnitPrice））}'PASSINGOBJECT_VALUE）ttab;

　　在以上示例中，您在XMLTable函数的PASSING子句中使用OBJECT_VALUE虚拟列将purchaseorder表作为上下文项传递给此处使用的XQuery表达式。

XQuery表达式计算用户EABEL请求的每个购买订单的总计，并为处理的每个订单生成一个OrderTotalXML元素。

要访问生成的XML，请使用SELECT列表中的COLUMN_VALUE虚拟列。

最终的输出应如下所示：

　　ORDERTOTALEABEL-20021009123338324PDT1328.05EABEL-20021009123335791PDT2067.15EABEL-20021009123336251PDT289.6EABEL-20021009123336382PDT928.92

　　要获得相同的最终结果，可以改用XMLQuery函数。

但如果将上一个示例中使用的XQuery表达式参数传递给XMLQuery（如下所示）：

　　SELECTXMLQuery（'for$iin/PurchaseOrderwhere$i/Usereq"EABEL"return{$i/Reference}{fn:

sum（for$jin$i/LineItems/LineItem/Partreturn（$j/@Quantity*$j/@UnitPrice））}'PASSINGOBJECT_VALUERETURNINGCONTENT）FROMpurchaseorder;

　　则XQuery表达式返回的空序列将与purchaseorder表联接，从而包含在查询总结果集中。

实际上，这意味着输出将不仅包含为用户EABEL请求的订单生成的OrderTotal元素，而且还包含为purchaseorder表中存储的所有其他订单生成的空行（默认情况下，purchaseorder表包含132行）。

从结果集中排除空行的方法之一是在SELECT语句的WHERE子句中使用existsNodeSQL函数，而不是在XQuery表达式中使用WHERE子句，如下所示：

　　SELECTXMLQuery（'for$iin/PurchaseOrderreturn{$i/Reference}{fn:

sum（for$jin$i/LineItems/LineItem/Partreturn（$j/@Quantity*$j/@UnitPrice））}'PASSINGOBJECT_VALUERETURNINGCONTENT）ASordertotalFROMpurchaseorderWHEREexistsNode（OBJECT_VALUE,'/PurchaseOrder[User="EABEL"]'）=1;

　　以上查询与本部分开头的XMLTable示例生成相同的输出。

　　查询OracleXMLDB信息库中的XML数据

　　为访问OracleXMLDB信息库中存储的XML数据，OracleXQuery引入了fn:

doc和fn:

collectionXQuery函数。

使用fn:

doc，您可以查询XML信息库中存储的单个XML文档，而fn:

collection使您可以访问同一信息库文件夹中存储的多个XML文档。

　　正如本文之前（参阅使用关系数据构建XML部分）介绍的示例所演示，使用fn:

doc非常简单直接。

它获取表示信息库文件资源（URI）的字符串并返回该URI指向的文档。

要了解fn:

collectionXQuery函数的作用，同一文件夹中至少应有两个信息库文件。

如果已经运行了列表1中的代码，则已经创建了/public/employees信息库文件夹并在其中存储了employees.xml文件。

因此，您将需要在该文件夹中至少再创建一个XML文件，然后才能试用fn:

collection。

列表2中的PL/SQL代码基于SCOTT/TIGER演示数据库模式的dept和emp表存储的关系数据构建XML，然后将生成的XML文档作为acc_dept.xml保存到/public/employees信息库文件夹。

要运行列表2中的PL/SQL过程，请确保以SCOTT/TIGER的身份登录。

　　列表2：

基于关系数据构建XML并将其保存到XML信息库

　　DECLAREXMLdocXMLType;BEGINSELECTXMLQuery（'for$jinora:

view（"SCOTT","dept"）/ROWwhere$j/DEPTNO=10return（{$j/DEPTNO,$j/DNAME}{for$iinora:

view（"SCOTT","emp"）/ROWwhere$i/DEPTNO=$j/DEPTNOreturn（{$i/EMPNO,$i/ENAME,$i/SAL}）}）'RETURNINGCONTENT）INTOXMLdocFROMDUAL;IF（DBMS_XDB.CREATERESOURCE（'/public/employees/acc_dept.xml',XMLdoc））THENDBMS_OUTPUT.PUT_LINE（'Resourceiscreated'）;ELSEDBMS_OUTPUT.PUT_LINE（'Cannotcreateresource'）;ENDIF;COMMIT;END;/

　　此时，/public/employees信息库文件夹应包含两个文件：

acc_dept.xml（由列表2中的PL/SQL代码生成）和employees.xml文件（由列表1中的代码生成）。

由于这些XML文档存储在同一信息库文件夹中，因此可以使用fn:

collection函数访问两个XML文档中存储的员工信息。

然而，尽管这些XML文档均包含员工XML元素（这些元素实际上具有相同结构），但XML文档本身的结构迥然不同。

在employees.xml中，文档根元素为EMPLOYEES，而acc_dept.xml将DEPARTMENT用作根元素。

要解决此问题，可以通过XQuery使用XPath//构造，从而导航到XML文档中的某个节点，而不必指定该节点的确切路径。

以下示例演示了如何在XQuery表达式中使用XPath//构造：

　　SELECTXMLQuery（'for$iinfn:

collection（"/public/employees"）//EMPLOYEEwhere$i/SAL>=5000orderby$i/ENAMEreturn;$i'RETURNINGCONTENT）FROMDUAL;

　　该构造应生成以下输出：

　　102DeHaan170007839KING5000100King24000101Kochhar17000

　　您可以看到，以上输出包含从employees.xml和acc_dept.xml中获取的员工XML元素，这些元素表示薪酬大于或等于5,000美元的员工。

　　将XML分解为关系数据

　　如果应用程序处理关系数据而非XML，而您需要访问的数据以XML格式存储，则将XML分解为关系数据可能会非常有用。

继续进行上一部分的示例，您可以使用SQL函数XMLTable将员工XML元素分解为虚拟表的单个列，如下所示：

　　SELECTemps.empno,emps.ename,emps.salFROMXMLTable（'for$iinfn:

collection（"/public/employees"）//EMPLOYEEwhere$i/SAL>=5000return;$i'COLUMNSempnoNUMBERPATH'/EMPLOYEE/EMPNO',enameVARCHAR2（30）PATH'/EMPLOYEE/ENAME',salNUMBERPATH'/EMPLOYEE/SAL'）emps;

　　该查询将生成以下输出：

EMPNOENAMESAL-----------------------------7839KING5000100King24000101Kochhar17000102DeHaan17000

　　查询外部数据源

　　使用XQuery，可以基于XML数据以及可以用XML表示的非XML数据生成XML文档，无论其位置如何：

无论是存储在数据库中、置于网站上、即时创建还是存储在文件系统中。

但要注意，OracleXMLDB为针对数据库中存储的数据进行的XML操作提供了非常高的性能和可伸缩性。

因此，如果您能够完全控制所处理的数据，则最好将它移动到数据库中。

　　正如您从前面的示例中了解到的，在OracleXQuery实施中，doc和collectionXQuery函数用于访问OracleXMLDB信息库中存储的XML文档。

可以通过XMLTable和XMLQuerySQL函数中的PASSING子句动态绑定外部数据源。

考虑以下示例。

假设您的公司要为那些致力于XQ项目的员工支付奖金。

因此，财务部发布了empsbonus.xml文件，其中包含有资格获得奖金的员工列表以及该列表中输入的每个员工的奖金数额。

empsbonus.xml文件可能如下所示：

　　10012001011000

　　在实际情况中，以上的XML文件可能置于网站上（因此可以通过互联网获得）、以文件形式存储在本地文件系统中，或以文件资源形式存储在OracleXMLDB信息库中。

就本示例而言，该文件位于网站上。

为简单起见，可以在目录（Web服务器在其中存储可从Web看到的文档）中创建一个员工文件夹，然后在该文件夹中插入empsbonus.xml文件，以便可以通过以下URL访问empsbonus.xml文件：

　　http:

//localhost/employees/empsbonus.xml

　　接下来，假设您需要基于empsbonus.xml文档中存储的数据创建一个报表。

在该报表中，您可能不但要包含列表中显示的奖金数额以及每个员工的员工ID，还要包含他/她的全名。

因此，可以首先使用以下查询生成一个新的XML文档（假设您以HR/HR的身份连接）：

　　SELECTXMLQuery（'for$kin1return（{for$iinora:

view（"employees"）/ROW,$jin$emps/EMPLOYEES/EMPLOYEEwhere$i/EMPLOYEE_ID=$j/EMPNOreturn（{xs:

string（$i/EMPLOYEE_ID）}{xs:

string（fn:

concat（$i/FIRST_NAME,"",$i/LAST_NAME））}{xs:

integer（$j/BONUS）}）}）'PASSINGxmlparse（documenthttpuritype（'http:

//localhost/employees/empsbonus.xml'）.getCLOB（））as"emps"RETURNINGCONTENT）.getStringVal（）asRESULTFROMDUAL;

　　以上查询是一个有关如何使用XQuery基于XML和非XML数据（以不同的方式从不同的数据源中检索）生成XML文档的示例。

具体而言，使用ora:

view（）函数访问HR演示模式中的默认employees关系表，并使用PASSING子句中的httpuritype（）函数借助于HTTP访问empsbonus.xml文档。

然后，在FLWOR表达式的return子句中构建新的XML文档。

最后，将获得以下XML文档：

　　100StevenKing1200101NeenaKochhar1000

　　解决性能问题

　　正如您从前面的部分中了解到的，XQuery是一种用于查询Oracle数据库存储的XML内容的高效方法-无论您是处理本地存储的XMLType数据还是查询基于关系数据构建的XML视图。

但根据对数据使用的存储类型的不同，XQuery表达式的执行性能可能迥然不同。

尤其是，OracleXMLDB可以优化基于由ora:

view函数创建的SQL/XML视图而构建的XQuery表达式。

对于XMLType表或列中存储的XML数据，只能对使用结构化（对象-关系）存储技术存储的基于XML模式的XMLType数据进行XQuery优化。

　　所选择的存储模型并非是影响XQuery表达式执行性能的唯一因素。

在某些情况下，XQuery表达式本身的结构也可能导致性能问题。

要监控XQuery表达式的性能，可以打印并检查关联的EXPLAINPLAN。

在SQL*Plus中，只需设置AUTOTRACE系统变量，即可打印SQL优化程序使用的执行路径。

但要执行该操作，请确保创建PLUSTRACE角色，然后将其授予连接到数据库所使用的用户。

有关如何执行此操作的信息，请参阅Oracle数据库10g第2版（10.2）文档中《SQL*Plus用户指南和参考》一书中的“调整SQL*Plus”一章。

以下示例演示了如何通过检查EXPLAINPLAN生成的执行计划来获得好处。

假设您已经将PLUSTRACE角色授予默认用户OE，以OE/OE的身份登录并运行以下查询：

　　SETAUTOTRACEONEXPLAIN

　　SELECTcount（*）FROMoe.purchaseorder,XMLTable（'for$iin/PurchaseOrder/Userwhere$i="CJOHNSON"return$i'PASSINGOBJECT_VALUE）ptab;

　　这将生成以下输出：

　　COUNT（*）----------9

　　ExecutionPlan--Planhashvalue:

4046110317

　　Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|--|0|SELECTSTATEMENT||1|226|29（0）|00:

00:

01|

　　|1|SORTAGGREGATE||1|226|||

　　|2|NESTEDLOOPS||10782|2379K|29（0）|00:

00:

01|

　　|*3|TABLEACCESSFULL|PURCHASEORDER|1|226|5（0）|00:

00:

01|

　　|4|COLLECTIONITERATORP|XMLSEQUENCEFROMX|||||

　　PredicateInformation（identifiedbyoperationid）:

　　3-filter（SYS_CHECKACL（"ACLOID","OWNERID",xmltype（'...

　　您可能对为以上查询生成的执行计划并不满意。

尤其是，所处理的行数可能非常大。

由于SQL调整的主要目标是避免访问对结果没有任何影响的行，因此可能要继续调整查询以优化性能。

对查询中包含的XPath表达式进行重新建模后，可以再次重试它，如下所示：

　　SELECTcount（*）FROMoe.purchaseorder,XMLTable（'for$iin/PurchaseOrderwhere$i/User="CJOHNSON"return$i/User'PASSINGOBJECT_VALUE）ptab;

　　这次，输出应如下所示：

COUNT（*）----------9

　　ExecutionPlan-Planhashvalue:

3411896580

　　Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time0|SELECTSTATEMENT||1|29|7（0）|00:

00:

01|

　　|1|SORTAGGREGATE||1|29|||

　　|2|NESTEDLOOPS||1|29|7（0）|00:

00:

01