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

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

有关 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 系统过程。

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
...	...	...	...

到目前为止，已对通过诸如 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，您可以使用 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 子句。

使用 openxml 导入 XML

使用 XPath 表达式

使用 openxml 生成结果集

使用 openxml 生成边缘表

搭配使用 openxml 和 xp_read_file

查询列中的 XML