使用 openxml 导入 XML

在您使用 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

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

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

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

CREATE TABLE IF NOT EXISTS xmldata (xmldoc XML);
INSERT INTO xmldata WITH AUTO NAME
    SELECT XMLELEMENT( NAME root,
         (SELECT EmployeeID, Employees.GivenName, Employees.Surname,
           Customers.ID, Customers.GivenName, Customers.Surname, Customers.Phone, CompanyName,
           SalesOrders.ID, OrderDate, Region
         FROM Employees
         KEY JOIN SalesOrders        
         KEY JOIN Customers
         ORDER BY EmployeeID, Customers.ID, SalesOrders.ID
         FOR XML AUTO, ELEMENTS)) AS xmldoc;
SELECT xmldoc FROM xmldata;

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

<root>
  <Employees>
    <EmployeeID>129</EmployeeID>
    <GivenName>Philip</GivenName>
    <Surname>Chin</Surname>
     
    <Customers>
      <ID>101</ID>
      <GivenName>Michaels</GivenName>
      <Surname>Devlin</Surname>
      <Phone>2015558966</Phone>
      <CompanyName>The Power Group</CompanyName>
        <SalesOrders>
          <ID>2560</ID>
          <OrderDate>2001-03-16</OrderDate>
          <Region>Eastern</Region>
        </SalesOrders>
    </Customers>
      
    <Customers>
      <ID>103</ID>
      <GivenName>Erin</GivenName>
      <Surname>Niedringhaus</Surname>
      <Phone>2155556513</Phone>
      <CompanyName>Darling Associates</CompanyName>
        <SalesOrders>
          <ID>2451</ID>
          <OrderDate>2000-12-15</OrderDate>
          <Region>Eastern</Region>
        </SalesOrders>
    </Customers>
      
    <Customers>
      <ID>104</ID>
      <GivenName>Meghan</GivenName>
      <Surname>Mason</Surname>
      <Phone>6155555463</Phone>
      <CompanyName>P.S.C.</CompanyName>
        <SalesOrders>
          <ID>2331</ID>
          <OrderDate>2000-09-17</OrderDate>
          <Region>South</Region>
        </SalesOrders>
        
        <SalesOrders>
          <ID>2342</ID>
          <OrderDate>2000-09-28</OrderDate>
          <Region>South</Region>
        </SalesOrders>
    </Customers>
    ...
  </Employees>
  ...
  <Employees>
  ...
  </Employees>
</root>

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

CREATE OR REPLACE VARIABLE x XML;
SELECT xmldoc INTO x FROM xmldata;

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	129
55	16	GivenName	Phillip
82	16	Surname	Chin
...	...	...	...

到目前为止，已对通过诸如 XMLELEMENT 之类的过程生成的 XML 进行了使用。您还可以从文件中读取 XML 并使用 xp_read_file 过程对其进行分析。假定文件 c:\temp\inventory.xml 是使用以下查询编写而成的。

SELECT xp_write_file( 'c:\\temp\\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：

SELECT * 
FROM openxml( xp_read_file( 'c:\\temp\\inventory.xml' ),
  '//*' )
WITH (ID INT '@mp:id',
       parent INT '../@mp:id',
       name CHAR(128) '@mp:localname',
       text LONG VARCHAR 'text()' )
ORDER BY ID;

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

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

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

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

SELECT *
FROM xmltest
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 xmltest, LATERAL( openxml( xmltest.Reports, '//e' )
WITH (EmployeeID INT '.') ) DerivedTable
ORDER BY ManagerID, EmployeeID;

此查询会生成以下结果：

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

使用 openxml 导入 XML

使用 XPath 表达式

使用 openxml 生成结果集

使用 openxml 生成边缘表

搭配使用 openxml 和 xp_read_file

查询列中的 XML

另请参见