oracle操作xml笔记四

使用 fn:doc 和 fn:collection 函数在 XML DB 信息库中查询 XML 数据

有两个重要的 XQuery 函数可用于在 XML DB 信息库中查询所有资源。Fn:doc 是一个 XQuery 函数，可以获取包含 XML 数据的信息库文件。该文件资源由其 URL 参数指出。XQuery 变量可以使用 FLWOR 表达式 FOR 和 LET 绑定到数据。XQuery 函数 fn:doc 可用于读取存储在 XML DB 信息库中的单个 XML 文档。

在信息库中查询资源的第二个 XQuery 函数是 fn:collection。该函数可以返回存储在信息库中同一文件夹中的大量类似文档。

现在，我们将通过创建信息库资源来演示 fn:doc 和 fn:collection 如何处理一些简单示例。首先，使用 DBMS_XDB PL/SQL 程序包创建一个资源。这可用于在 XML DB 中管理资源。该 createResource 过程在本例中用于创建包含 Orders 和 Partys XML 文档的新文件资源。

创建用于以下示例的定单和定方资源名。其中有一个名为 ordersnamespace.xml 的附加资源，它是一个使用命名空间的示例。

DECLARE
  res BOOLEAN;
  ordersxmlstring VARCHAR2(500):= 
    '<?xml version="1.0"?>
     <orders>
       <order orderno="15" partyno="1111" itemname="Widget" amt="5000"/>
       <order orderno="25" partyno="1111" itemname="Do dad" amt="2000"/>
       <order orderno="35" partyno="2222" itemname="All purpose item" amt="7000"/>
       <order orderno="45" partyno="3333" itemname="The best thing" amt="15000"/>
     </orders>';
  partysxmlstring VARCHAR2(500):=
    '<?xml version="1.0"?>
     <partys>
       <party partyno="1111" partyname="ABC Corp" partycity="Toronto"/>
       <party partyno="2222" partyname="Freds Inc" partycity="Chicago"/>
       <party partyno="3333" partyname="Gofaster Corp" partycity="Montreal"/>
     </partys>';
  ordersxmlnsstring VARCHAR2(500):=
    '<?xml version="1.0"?>
     <orders xmlns="http://order.com">
       <order orderno="15" partyno="1111" itemname="Widget" amt="5000"/>
       <order orderno="25" partyno="1111" itemname="Do dad" amt="2000"/>
       <order orderno="35" partyno="2222" itemname="All purpose item" amt="7000"/>
       <order orderno="45" partyno="3333" itemname="The best thing" amt="15000"/>
     </orders>';
BEGIN
  res := DBMS_XDB.createResource('/public/orders.xml',   ordersxmlstring);
  res := DBMS_XDB.createResource('/public/partys.xml',  partysxmlstring);
  res := DBMS_XDB.createResource('/public/ordersnamespace.xml', ordersxmlnsstring);
END;

我们可以看到刚刚通过查询 resource_view 创建的资源，如下所示：

SQL>  select any_path, res from resource_view where any_path like '%partys%';
ANY_PATH

RES

/public/partys.xml
<Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd">
  <CreationDate>2006-06-19T17:12:00.414000</CreationDate>
  <ModificationDate>2006-06-19T17:12:00.414000</ModificationDate>
  <DisplayName>partys.xml</DisplayName>
  <Language>en-US</Language>
  <CharacterSet>WINDOWS-1252</CharacterSet>
  <ContentType>text/xml</ContentType>
  <RefCount>1</RefCount>
</Resource>

接下来，使用 fn:doc 显示单个 partys.xml 文档。

SELECT XMLQuery('for $p in fn:doc("/public/partys.xml") 
                 return $p'
                RETURNING CONTENT) partys FROM DUAL;

PARTYS
---------------------------------------------------------------------
<partys><party partyno="1111" partyname="ABC Corp" 
partycity="Toronto"/><party partyno="2222" partyname="Freds Inc" 
partycity="Chicago"/> <party partyno="3333" partyname="Gofaster Corp"
partycity="Montreal"/></partys>

对 Fn:collection 的使用与 fn:doc 稍有不同，它可返回存储在信息库中同一文件夹中的所有文档。从该语句生成的输出不适合打印，手动进行以下格式化以使该文档可读性更高。

SELECT XMLQuery('for $p in fn:collection("/public") 
                 return $p'
                RETURNING CONTENT) collection_public FROM DUAL;

COLLECTION_PUBLIC
----------------------------------------------------------------------
<orders>
   <order orderno="15" partyno="1111" itemname="Widget" amt="5000"/>
   <order orderno="25" partyno="1111" itemname="Do dad" amt="2000"/>
   <order orderno="35" partyno="2222" itemname="All purpose item" amt="7000"/>
   <order orderno="45" partyno="3333" itemname="The best thing" amt="15000"/>
</orders>

<orders xmlns="http://order.com">
   <order orderno="15" partyno="1111" itemname="Widget" amt="5000"/>
   <order orderno="25" partyno="1111" itemname="Do dad" amt="2000"/>
   <order orderno="35" partyno="2222" itemname="All purpose item" amt="7000"/>
   <order orderno="45" partyno="3333" itemname="The best thing" amt="15000"/>
</orders>

<partys>
   <party partyno="1111" partyname="ABC Corp" partycity="Toronto"/>
   <party partyno="2222" partyname="Freds Inc" partycity="Chicago"/>
   <party partyno="3333" partyname="Gofaster Corp" partycity="Montreal"/>
</partys>

还有其他大量 DBMS_XDB 过程和函数可用于管理所有 XML DB 资源，例如，提供删除资源和创建文件夹的功能。这些函数可在 OTN 上的 Oracle XML DB 开发人员指南 10 g 第 2 版 中找到。

XMLQuery() 和 XMLTable()

Oracle 数据库 10g 第 2 版中引入了 XMLQuery() 和 XMLTable() 函数。它们在 SQL 和 XML 之间提供了强大的接口，允许我们查询、构造和转换关系数据，就像它是 XML，反之也可以将 XML 数据当作关系数据来处理。通常，我们使用 XMLQuery() 从关系数据生成 XML 文档，并使用 XMLTable() 从 XML 数据创建关系视图。它们仍然不是 ANSI SQL 的一部分，但预计会包括在将来的标准中。

XMLQuery() 函数。 该函数用于构造 XML 数据并使用 XQuery 来查询该数据。它支持在 SQL 上下文中执行 XQuery 表达式。我们也可以通过使用 ora:view 为关系数据创建动态 XML 视图来查询关系数据，并能够操作部分 XML 文档而非整个文档。

XMLQuery() 将 XQuery 表达式作为字符串文字，将可选的 XQuery“上下文项”作为 SQL 表达式。该表达式设置将在其中评估 XQuery 表达式的 XPath 上下文。该函数将 XQuery 表达式结果作为 XMLType 实例返回。XMLQuery() 函数也可以将 SQL 表达式作为参数，其中当评估表达式时，值绑定到 XQuery 变量。这些结果作为 XMLType 实例返回。

来看两个示例，其中我们针对关系数据和 XML 数据使用了 XMLQuery()。

• 将 XMLQuery() 与 ora:view 和 FLWOR 表达式合并的示例。 在 第一个示例中，我们将在物理关系表上使用 XMLQuery()。XMLQuery() 必须操作 XML 数据，这可通过使用 HR 模式中关系表 Employee 和 Department 上的 ora:view 特性完成。当 ora:view 用在这两个关系表上之后，它们将显示为 XML，然后我们就能够使用 XQuery 表达式，包括嵌套的 FLWOR 表达式。

下面的查询正在进行以下操作：对于每个部门，获取部门 id，对于匹配部门 id 且佣金高于 30% 的雇员，返回雇员的名和姓。请注意，FOR 表达式在该查询中使用了两次。

SELECT XMLQuery(
         'FOR $dep in ora:view("DEPARTMENTS")/ROW
          RETURN <Department id="{$dep/DEPARTMENT_ID}">
                 <Employee>
                   {FOR $emp in ora:view("EMPLOYEES")/ROW 
                    WHERE $emp/DEPARTMENT_ID eq $dep/DEPARTMENT_ID
                      and $emp/COMMISSION_PCT > .3 
                    RETURN ($emp/FIRST_NAME, $emp/LAST_NAME)}
                </Employee>
                </Department>'
          RETURNING CONTENT) HIGH_COMMISSION_EMP_NAMES FROM DUAL;

HIGH_COMMISSION_EMP_NAMES
---------------------------------------------------------------
<Department id="10"><Employee></Employee></Department><Department 
id="20"><Employee></Employee></Department><Department 
id="30"><Employee></Employee></Department><Department 
id="40"><Employee></Employee></Department><Department 
id="50"><Employee></Employee></Department><Department 
id="60"><Employee></Employee></Department>

正如您所见，结果不适合打印。该查询语法有点复杂，但它显示出我们能够使用 ora:view 将关系数据转换为 XML，并能够针对 XML 文档执行连接操作以及将谓词应用到文档中的数据上。

• 将 XMLQuery() 与 XMLType 列和 FLWOR 表达式一起使用的示例。 表 invoicexml_col（我们在上面创建的，并在其中插入了一个文档）包含一个 XMLType 列 inv_doc。存储在该列中的 Invoice 数据是 XML 格式的。在该查询中，我们使用具有 PASSING 子句的 XMLQuery() 函数将 XMLType 列 inv_doc 传递到 XQuery。请注意我们是如何从基于 WHERE 谓词的 Invoice 文档返回一些特定字段的。实际上，我们能够进入 XML 文档并根据谓词检索特定字段，而非只能够将此文档作为 CLOB 查看。

下面的 SELECT 语句适用于 invoicexml_col 的所有行。然后我们可以使用 FOR 语句迭代所有发票行。WHERE 谓词取出 Zipcode 12345 的元素，然后返回 City、State 和 Zipcode，以及是否为 Zipcode 输入了正确的 City 和 State。 IF…THEN…ELSE 构造将体现该语法的一些附加功能。

Select XMLQuery(
         'FOR $i in /Invoice 
          WHERE  $i/MailAddressTo/Zipcode = 12345 
          RETURN <Details>
                   <Zipcode num="{$i/MailAddressTo/Zipcode}"/>
                   <CityName char="{$i/MailAddressTo/City}"/>
                   <City>{IF ($i/MailAddressTo/City = "New York") 
                          THEN "Correct City" 
                          ELSE "Incorrect City"}
                   </City>
                   <State>{if ($i/MailAddressTo/State = "NY") 
				   then "Correct State" else "Incorrect State"}
                   </State>
                 </Details>'
         PASSING inv_doc RETURNING CONTENT) ny_invoice
FROM invoicexml_col;

NY_INVOICE

<Details><Zipcode num="12345"></Zipcode>
<CityName char="New York"></CityName>
<City>Correct City</City>
<State>Correct State</State>
</Details>

这两个查询阐释了我们必须以详细的分段方式查看和处理 XML 文档的功能。提供的功能类似于我们用关系数据进行的操作 — 令人惊奇!

XMLTable() 函数。 该函数支持将 XML 值解释为表或集。它用于从 XQuery 表达式的评估返回表和列，而不是像通常那样返回一个序列作为 XQuery。可以查询 XMLType 数据并将 XML 结果分割或分解为关系格式 — 可将其看作是创建一个虚拟表。然后，可以使用该虚拟表将数据插入到其他表中，或者查询该虚拟表。关系视图也可以针对 XML 数据进行构造。XMLTable() 函数也可用在 SQL From 子句中。

• 将 XMLTable() 与 COLUMNS 子句一起使用的示例 。 我们将 使用前面介绍过 invoicexml_col 表来阐释如何使用 XMLTable() 将 XML 数据转换为关系格式。在下面的示例中，XMLTable() 访问存储在列 inv_doc 中的 Invoice 文档。使用 COLUMNS 子句将所需数据元素的路径映射到新的名称和格式。 XMLTable() 函数返回数据作为虚拟表，该查询的结果与我们已经查询过的关系表一样。请注意，通过在该查询底部使用的 WHERE 子句，我们可以过滤 XML 数据，过滤的方式与使用任何为关系数据编写的 SQL 查询的方式完全相同。

该查询和输出如下所示：

SELECT inv_id, a.PersonName, a.StreetName, a.CityName, a.State, a.Zipcode
FROM invoicexml_col,  
     XMLTABLE('/Invoice'
     PASSING invoicexml_col.inv_doc
     COLUMNS 
        PersonName varchar2(10) PATH '/Invoice/MailAddressTo/Person',
        StreetName varchar2(20) PATH '/Invoice/MailAddressTo/Street',
        CityName varchar2(10) PATH '/Invoice/MailAddressTo/City',
        State varchar2(5) PATH '/Invoice/MailAddressTo/State',
        Zipcode varchar2(7) PATH '/Invoice/MailAddressTo/Zipcode'
      ) a
WHERE a.CityName like 'New%';

INV_ID
	PERSONNAME
	STREETNAME
	     CITYNAME
	STATE
 	ZIPCODE

    1	Joe Smith  	10 Apple Tree Lane      New York	 NY	 12345

执行 XML

如文中所述，我们已经可以执行查看 XML 文档的查询。下一步是考虑使这些查询快速执行的方式。可以解释 XML 查询并创建索引来帮助提高 XML 数据访问的性能。特别地，我们能够以类似过去调整 SQL 的方式改善 XPath 函数的性能。此外，对于 SQL 调整，在某些情况中，重构 XML 查询也有助于更改和改进访问路径。

基于函数的索引可用在结构化和非结构化的 XMLType 表上，无论它们是否基于模式。我们还可能想对诸如 existsNode 的函数利用二进制索引，即根据谓词评估为 True 或 False 而只返回标志 0 或 1。

在本节中，我们将通过示例来了解一下结构化和非结构化索引通过解释计划会对性能产生怎样的潜在影响。首先，优化 XQuery 表达式，该表达式针对关系表使用 ora:view。

用 XQuery 表达式优化关系数据。 以下是一个调整示例，其中使用的是已分析过的类似查询。该查询使用 ora:view 访问关系数据并利用 FLWOR 表达式。连接 HR 模式的关系表 Employees 和 Departments，并针对 commission_pct 大于 3 的所有员工返回部门信息。请注意，属性名（例如，$emp/ROW/COMMISSION_PCT 中的 JOB）区分大小写。

在下面的查询中，FOR 允许我们在 Departments 中迭代行元素 Employees。Employees 行绑定到变量 $emp，而 Departments 行绑定到 $dep。WHERE 执行这两个表的连接，并选择 commission_pct 大于 3 的所有员工。RETURN 返回部门信息。

该查询和解释计划如下所示：

explain plan for 
SELECT XMLQuery('for $emp in ora:view("EMPLOYEES"), $dep in ora:view("DEPARTMENTS")
                 where $emp/ROW/DEPARTMENT_ID = $dep/ROW/DEPARTMENT_ID
                   and $emp/ROW/COMMISSION_PCT > .3
                 return $dep'   
                RETURNING CONTENT) AS high_commission_employees
FROM DUAL;

QUERY_PLAN              OBJECT_NAME	         COST     BYTES	LEVEL

SELECT STATEMENT                               2                  1
  SORT                                                 82         2
    HASH JOIN                                  5       328        3
      TABLE ACCESS      EMPLOYEES              2       104        4
      TABLE ACCESS      DEPARTMENTS            2       1512       4
  FAST DUAL                                    2                  2

如您所见，优化程序选择的访问路径不使用索引。由于 ora:view 允许我们将关系查询的结果返回为 XML 元素，因此我们完全可以在关系表上创建 b 树索引并查看这是否可以提高性能。

Create index emp_idx1 on employees (department_id, commission_pct);
Create index dept_idx1 on departments (department_id);

QUERY_PLAN          OBJECT_NAME		COST     BYTES	LEVEL

SELECT STATEMENT                               2                  1
  SORT                                                 82         2
    TABLE ACCESS    DEPARTMENTS                1       56         3
    NESTED LOOPS                               2       328        4
    INDEX           EMP_IDX1                   1       104        5
    INDEX           DEPT_IDX1                  0                  5
  FAST DUAL                                    2                  2

现在，这些索引获得了使用，成本略有降低。我们使用 ora:view 对 XQuery 的调整非常类似于对标准的非 XML SQL 的调整。对于标准 SQL，重写该查询并使用诸如绑定变量（而非硬编码的字符串值）的特性将有助于提高性能。

优化结构化 XMLType 数据。 XPath 重写可以优化结构化（对象关系）存储技术。该优化程序可以进行另一个内部性能改进：当以下内容为真时，它可以将基于 XPath 的函数更改为关系语句：

• XMLType 数据使用一个注册的 XMLSchema。这是使用基于模式的数据的另一个优势。
• 属性映射为基础 SQL（关系）表。
• 使用了结构化存储。

这提供了由于性能原因进行查询重写的可能性。它也允许使用关系性能调整技术，如上所示。

现在，我们优化针对结构化、非关系 XMLType 数据的查询。首先看一个非常简单的示例，它使用早前创建的 invoicexml_tbl 表。

explain plan for 
select extract(object_value, '/Invoice/MailAddressTo')
from invoicexml_tbl
where extractValue(object_value, '/Invoice/MailAddressTo/Person')= 'Joe Smith';

The access path is shown below.


QUERY_PLAN        OBJECT_NAME          COST      BYTES	LEVEL

SELECT STATEMENT                        3        87       1
  TABLE ACCESS    INVOICEXML_TBL        3        87       2

可以创建该查询中使用的针对谓词的基于函数的索引。该索引必须使用与将使用它的查询完全相同的语法。该索引将创建并多次解释 SQL 以查看是否使用了新索引。

Create index invoicexml_tbl_idx1 on invoicexml_tbl
(extractValue(object_value, '/Invoice/MailAddressTo/Person'));

QUERY_PLAN        OBJECT_NAME           COST    BYTES	LEVEL

SELECT STATEMENT                         1      87        1
  TABLE ACCESS    INVOICEXML_TBL         1      87        2
  INDEX           INVOICEXML_TBL_IDX1    1                3

使用了新的基于函数的索引后，查询成本将减少。

优化非结构化 XMLType 数据。
现在我们将执行与非结构化 XMLType 数据相同的测试。表 invtest_unstruct 的创建方式将与我们创建 invoicexml_tbl 的方式完全相同，但我们会添加“XMLType store as CLOB”语法以确保将它定义为非结构化数据。数据定义语言 (DDL)、插入和解释计划如下所示：

create table invtest_unstruct of XMLtype 
XMLType store as CLOB;

Insert into invtest_unstruct values ( 
XMLType(bfilename('XMLDIR', 'invoicexml.txt'), 
nls_charset_id('WE8MSWIN1252') )); 

explain plan for 
select extract(object_value, '/Invoice/MailAddressTo')
from invtest_unstruct
where extractValue(object_value, '/Invoice/MailAddressTo/Person')='Joe Smith'
/

QUERY_PLAN            OBJECT_NAME      	COST     BYTES	LEVEL

SELECT STATEMENT                               2       2002       1
  TABLE ACCESS        INVTEST_UNSTRUCT         2       2002       2

现在，我们将创建一个基于函数的索引，来查看是否将使用它以及是否减少了预期的成本。请注意，“create index”语法与查询谓词中使用的语法相同。

Create index invtest_unstruct_idx1 on invtest_unstruct
(extractValue(object_value, '/Invoice/MailAddressTo/Person'));

QUERY_PLAN           OBJECT_NAME              COST     BYTES 	LEVEL

SELECT STATEMENT                               1       2002	  1
  TABLE ACCESS       INVTEST_UNSTRUCT          1       2002   2
  INDEX              INVTEST_UNSTRUCT_IDX1     1           	  3

正如您看到的，相同的索引和查询可以有效用于结构化和非结构化数据。但需要注意到，结构化数据访问通常比非结构化访问更高效。

结论

Oracle 数据库已经快速发展为将 XML 标准与数据合并在内。Oracle9i 数据库引入了 XML DB 信息库和一个新数据类型 XMLType，后者提供 LOB 以及结构化存储选项。利用 Oracle XML DB 特性，通过将 URI 映射为数据库对象的层次模型，您能够在数据库中执行本机 XML 处理。通过使用节点和路径概念的“逻辑树”，可使用 XPath 表达式操作文档的单个元素。Oracle9i 数据库中还包括 XMLSchema 支持。

Oracle 数据库 10g 通过 W3C XML XQuery 语言增强了 XML 支持，该语言包括 XMLQuery() 和 XMLTable() 函数。这些特性的组合简化了 Oracle 数据库 10g 第 2 版中关系数据和 XML 数据间的相互转换使用。正如本文所述，SQL 查询可以操作 XML 数据，而 XML 查询现在能够访问关系数据。其中的关键是 Oracle 对 XMLQuery() 和 XMLTable() 的 SQL/XML 实现。

Oracle 的基于标准的方法也支持流行的协议（例如，FTP、HTTP 和 WebDAV），以便允许常用客户端工具和应用程序访问、编辑和发布存储在数据库中的 XML 数据。

通过本文，您可轻松增强自己在处理关系数据时获取的技巧，进而使用这些 SQL/XML 特性来处理 XML。其中重要的一点是，我们能够通过 Oracle 的索引、解释和存储特性来提高 XML 数据访问的性能。对于设计人员、开发人员和 DBA 来说，及时了解这个快速变化的技术是很关键的。