Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (中文) » SQL Anywhere 服务器 - SQL 的用法 » 数据库中的 XML » 在数据库中使用 XML » 导入 XML 文档作为关系数据

 

使用 openxml 导入 XML

在查询的 FROM 子句中使用 openxml 过程,以便从 XML 文档中生成结果集。openxml 使用 XPath 查询语言的子集从 XML 文档中选择节点。

使用 XPath 表达式

在您使用 openxml 时,会对 XML 文档进行分析,而结果会采用树形式建模。这个树由节点组成。XPath 表达式用于选择树中的节点。以下列表介绍了一些常用的 XPath 表达式:

  • /   指明 XML 文档的根节点

  • //   指明根的所有下级节点,包括根节点

  • .(一个句点)   指明 XML 文档的当前节点

  • .//   指明当前节点的所有子代,包括当前节点

  • ..   指明当前节点的父节点

  • ./@attributename   指明名称为 attributename 的当前节点的属性

  • ./childname   指明当前节点中名称为 childname 的子项

请看下面的 XML 文档:

<inventory>
  <product ID="301" size="Medium">Tee Shirt
    <quantity>54</quantity>
  </product>
  <product ID="302" size="One Size fits all">Tee Shirt
    <quantity>75</quantity>
  </product>
  <product ID="400" size="One Size fits all">Baseball Cap
    <quantity>112</quantity>
  </product>
</inventory>

<inventory> 元素是根节点。可使用以下 XPath 表达式来引用它:

/inventory

假定当前节点是 <quantity> 元素。可使用以下 XPath 表达式来引用此节点:

.

要查找作为 <inventory> 元素子项的所有 <product> 元素,请使用以下 XPath 表达式:

/inventory/product

如果当前节点是 <product> 元素,且您需要引用 size 属性,请使用以下 XPath 表达式:

./@size

有关 openxml 支持的 XPath 语法的完整列表,请参见openxml 系统过程

有关 XPath 查询语言的信息,请参见 [external link] http://www.w3.org/TR/xpath

使用 openxml 生成结果集

openxml 的第一个 xpath-query 参数每匹配一次,就会在结果集中生成一行。WITH 子句指定结果集的模式以及如何为结果集中的每一列找到值。例如,假定有以下查询:

SELECT * FROM openxml( '<inventory>
                         <product>Tee Shirt
                           <quantity>54</quantity>
                           <color>Orange</color>
                         </product>
                         <product>Baseball Cap
                 <quantity>112</quantity>
                           <color>Black</color>
                         </product>
                        </inventory>',
                       '/inventory/product' )
WITH ( Name CHAR (25) './text()',
       Quantity CHAR(3) 'quantity',
       Color CHAR(20) 'color');

第一个 xpath-query 参数是 /inventory/product,并且 XML 中有两个 <product> 元素,因此,此查询会生成两行。

WITH 子句指定其中包含三列:Name、Quantity 和 Color。这三列的值来自于 <product>、<quantity> 和 <color> 元素。以上查询会生成以下结果:

Name Quantity Color
Tee Shirt 54 Orange
Baseball Cap 112 Black

有关详细信息,请参见openxml 系统过程

使用 openxml 生成边缘表

openxml 过程可以用来生成边缘表,在这种表中,XML 文档中的每一个元素都会有对应的一行。您可能会希望生成一个边缘表,以便能够使用 SQL 来查询结果集中的数据。

以下 SQL 语句创建了一个变量 x,其中包含一个 XML 文档。该查询生成的 XML 包含一个名为 <root> 的根元素,它是使用 XMLELEMENT 函数生成的,另外还使用指定了 ELEMENTS 修饰符的 FOR XML AUTO 为 Employees、SalesOrders 和 Customers 表中的每一列生成元素。

有关 XMLELEMENT 函数的信息,请参见XMLELEMENT 函数 [String]

有关 FOR XML AUTO 的信息,请参见使用 FOR XML AUTO

CREATE VARIABLE x XML;
SET x=(SELECT XMLELEMENT( NAME root,
         (SELECT * FROM Employees
         KEY JOIN SalesOrders
         KEY JOIN Customers
         FOR XML AUTO, ELEMENTS)));
SELECT x;

生成的 XML 将如下所示(已对结果进行了格式化处理以便于阅读—由查询返回的结果是一个连续字符串):

<root>
 <Employees>
  <EmployeeID>299</EmployeeID>
  <ManagerID>902</ManagerID>
  <Surname>Overbey</Surname>
  <GivenName>Rollin</GivenName>
  <DepartmentID>200</DepartmentID>
  <Street>191 Companion Ct.</Street>
  <City>Kanata</City>
  <State>CA</State>
  <Country>USA</Country>
  <PostalCode>94608</PostalCode>
  <Phone>5105557255</Phone>
  <Status>A</Status>
  <SocialSecurityNumber>025487133</SocialSecurityNumber>
  <Salary>39300.000</Salary>
  <StartDate>1987-02-19</StartDate>
  <BirthDate>1964-03-15</BirthDate>
  <BenefitHealthInsurance>Y</BenefitHealthInsurance>
  <BenefitLifeInsurance>Y</BenefitLifeInsurance>
  <BenefitDayCare>N</BenefitDayCare>
  <Sex>M</Sex>
  <SalesOrders>
  <ID>2001</ID>
  <CustomerID>101</CustomerID>
  <OrderDate>2000-03-16</OrderDate>
  <FinancialCode>r1</FinancialCode>
  <Region>Eastern</Region>
  <SalesRepresentative>299</SalesRepresentative>
    <Customers>
    <ID>101</ID>
    <Surname>Devlin</Surname>
    <GivenName>Michael</GivenName>
    <Street>114 Pioneer Avenue</Street>
    <City>Kingston</City>
    <State>NJ</State>
    <PostalCode>07070</PostalCode>
    <Phone>2015558966</Phone>
    <CompanyName>The Power Group</CompanyName>
    </Customers>
  </SalesOrders>
</Employees>
...

以下查询使用下级或自身 (//*) XPath 表达式来匹配以上 XML 文档中的每一个元素,对于每一个元素,使用 id 元属性来获取节点的 ID,将父 (../) XPath 表达式与 ID 元属性搭配使用来获取父节点。localname 元属性用于获取每个元素的名称。元属性名称是区分大小写的,因此不能将 ID 或 LOCALNAME 用作元属性名称。

SELECT * FROM openxml( x, '//*' )
 WITH (ID INT '@mp:id',
       parent INT '../@mp:id',
       name CHAR(25) '@mp:localname',
       text LONG VARCHAR 'text()' )
ORDER BY ID;

此查询生成的结果集会显示 XML 文档中每个节点的 ID、父节点的 ID 以及各元素的名称和内容。

ID parent name text
5 (NULL) root (NULL)
16 5 Employees (NULL)
28 16 EmployeeID 299
55 16 ManagerID 902
79 16 Surname Overbey
... ... ... ...
搭配使用 openxml 和 xp_read_file

到目前为止,已对通过诸如 XMLELEMENT 之类的过程生成的 XML 进行了使用。您还可以从文件中读取 XML 并使用 xp_read_file 过程对其进行分析。假定文件 c:\inventory.xml 中包含以下内容:

<inventory>
   <product>Tee Shirt
      <quantity>54</quantity>
      <color>Orange</color>
   </product>
   <product>Baseball Cap
      <quantity>112</quantity>
      <color>Black</color>
   </product>
</inventory>

您可以使用以下语句读取并分析文件中的 XML:

CREATE VARIABLE x XML;
SELECT xp_read_file( 'c:\\inventory.xml' ) 
 INTO x;
SELECT * FROM openxml( x, '//*' )
 WITH (ID INT '@mp:id',
       parent INT '../@mp:id',
       name CHAR(128) '@mp:localname',
       text LONG VARCHAR 'text()' )
ORDER BY ID;
查询列中的 XML

如果您有一个表,它的某一列包含 XML,您可以使用 openxml 一次查询出该列中的所有 XML 值。使用横向派生表可以实现这一点。

以下语句将创建一个含有两列(ManagerID 和 Reports)的表。Reports 列包含从 Employees 表中生成的 XML 数据。

CREATE TABLE test (ManagerID INT, Reports XML);
INSERT INTO test
SELECT ManagerID, XMLELEMENT( NAME reports,
              XMLAGG( XMLELEMENT( NAME e, EmployeeID))) 
FROM Employees
GROUP BY ManagerID;

执行以下查询可查看 test 表中的数据:

SELECT * FROM test
ORDER BY ManagerID;

此查询会生成以下结果:

ManagerID Reports
501
<reports>
 <e>102</e>
 <e>105</e>
 <e>160</e>
 <e>243</e>
 ...
</reports>
703
<reports>
 <e>191</e> 
 <e>750</e>
 <e>868</e>
 <e>921</e>
 ...
</reports>
902
<reports>
 <e>129</e>
 <e>195</e>
 <e>299</e>
 <e>467</e>
 ...
</reports>
1293
<reports>
 <e>148</e>
 <e>390</e>
 <e>586</e>
 <e>757</e>
 ...
</reports>
... ...

以下查询使用横向派生表来生成一个含有两列的结果集:一列中列出了每位经理的 ID,另一列中列出了会向该经理报告的每位雇员的 ID:

SELECT ManagerID, EmployeeID
FROM test, LATERAL( openxml( test.Reports, '//e' )
WITH (EmployeeID INT '.') ) DerivedTable
ORDER BY ManagerID, EmployeeID;

此查询会生成以下结果:

ManagerID EmployeeID
501 102
501 105
501 160
501 243
... ...

有关横向派生表的详细信息,请参见FROM 子句