openxml 系统过程

openxml( xml-data,
 xpath [, flags [, namespaces ] ] )
WITH ( column-name column-type [ xpath ],... )

openxml( { USING FILE | USING VALUE }  xml-data,
 xpath [, flags [, namespaces ] ] )
WITH ( column-name column-type [ xpath ],... ) 
[ OPTION ( scan-option ) ]
[ AS ] correlation-name

scan-option :
ENCODING encoding
| BYTE ORDER MARK { ON | OFF }

WITH 子句 指定结果集的模式及如何在结果集中查找每一列的值。WITH 子句的 xpath 参数根据第二个参数中的 xpath 进行匹配。如果 WITH 子句表达式匹配多个节点，则只有文档顺序中的第一个节点被使用。如果该节点不是文本节点，则通过附加所有下级文本节点来获得结果。如果一个 WITH 子句表达式不与任何节点匹配，则该行的列为 NULL。
WITH 子句中的 xpath 参数可以是文字字符串或变量。

openxml WITH 子句的语法与从存储过程中进行选择的语法类似。

USING FILE | USING VALUE 使用 USING FILE 子句从文件装载数据。需要 DBA 或 READFILE 权限才能使用 USING FILE 子句。

USING VALUE 子句用于从任何类型为 CHAR、NCHAR、BINARY 或 LONG BINARY 的表达式中或 BLOB 字符串中装载数据。

xml-data 生成结果集所基于的 XML。可以是任意的字符串表达式，如常量、变量或列。
如果在输出中含有 NCHAR 列，则直接以 NCHAR 编码分析 xml-data。xpath 和 namespaces 参数也会进行转换并以 NCHAR 编码进行分析。
xpath 包含 XPath 查询的字符串。XPath 允许您指定描述要查询的 XML 文档结构的模式。此参数中包含的 XPath 模式将从 XML 文档中选择节点。每个与出现在第二个 xpath 参数中的 XPath 查询相匹配的节点都将在表中生成一行记录。
元属性只能在 WITH 子句的 xpath 参数中指定。一个元属性可在 XPath 查询中作为一个属性来访问。如果不指定 namespaces，则缺省情况下将前缀 mp 绑定到统一资源定位符（Uniform Resource Identifier，简称 URI）urn:ianywhere-com:sa-xpath-metaprop。如果指定了一个 namespaces，则此 URI 必须绑定到 mp 或某个其它前缀以访问查询中的元属性。元属性名称区分大小写。openxml 语句支持以下元属性：
- @mp:id 返回一个在 XML 文档中唯一的节点的 ID。当数据库服务器重新启动后，给定文档中给定节点的 ID 可能会更改。此元属性的值按文档的顺序递增。
- @mp:localname 返回节点名称中的本地部分，如果该节点没有名称则返回 NULL。
- @mp:prefix 返回节点名称中的前缀部分，如果该节点没有名称或名称没有前缀则返回 NULL。
- @mp:namespaceuri 返回该节点所属的命名空间的 URI，如果该节点不在命名空间中则返回 NULL。
- @mp:xmltext 以 XML 形式返回 XML 文档的子树。例如，当匹配了一个内部节点时，可使用此元属性来返回一个 XML 字符串，而不再是下级文本节点的连接值。

flags 当 WITH 子句中没有指定 XPath 查询时，指明在 XML 数据和结果集之间应使用的映射。如果没有指定 flags 参数，则缺省的行为是将属性映射到结果集中的列。flags 参数可为以下值之一：

值	说明
1	将 XML 属性映射到结果集中的列（缺省）。
2	将 XML 元素映射到结果集中的列。

namespace-declaration 一个 XML 文档。查询的作用域命名空间取自该文档的根元素。如果指定命名空间，则必须给出一个 flags 参数，即使已指定了所有的 xpath 参数。

column-name 结果集中列的名称。
column-type 结果集中列的数据类型。该数据类型必须与从 XML 文档中选择的值相兼容。
OPTION 子句 使用 OPTION 子句可指定要对输入文件使用的分析选项，如转义字符、分隔符、编码等。
- ENCODING 子句 ENCODING 子句允许您指定用于读取文件的编码。
  如果没有指定 ENCODING 子句，则如果值为 CHAR 或 BINARY 类型则假定用数据库字符集 (db_charset) 对值进行编码，而在值为 NCHAR 类型的情况下则假定使用 NCHAR 数据库字符集 (nchar_charset) 对值进行编码。
- BYTE ORDER MARK 子句 使用 BYTE ORDER MARK 子句指定字节顺序标记 (BOM) 是否出现在编码中。缺省情况下，此选项为 ON，即允许服务器在数据的开始位置搜索并解释字节顺序标记 (BOM)。如果 BYTE ORDER MARK 为 OFF，则服务器不搜索 BOM。
  如果输入数据经过编码，则必须指定 BYTE ORDER MARK 子句。
  
  如果指定了 ENCODING 子句：
  - 如果 BYTE ORDER MARK 选项为 ON，并且指定了具有某一种字节序（如 UTF-16BE 或 UTF-16LE）的 UTF-16 编码，则数据库服务器会在数据的开始位置搜索 BOM。如果 BOM 存在，会将它用于验证数据的字节排序方式。如果指定了错误的字节序，则返回错误。
  - 如果 BYTE ORDER MARK 选项为 ON，但指定的是没有显式字节序的 UTF-16 编码，则数据库服务器将在数据的开始位置搜索 BOM。如果 BOM 存在，会将它用于确定数据的字节排序方式。否则，会将其假定为操作系统的字节排序方式。
  - 如果 BYTE ORDER MARK 选项为 ON，并指定了 UTF-8 编码，则数据库服务器会在数据的开始位置搜索 BOM。如果 BOM 存在，则会被忽略。
  如果未指定 ENCODING 子句：
  - 如果未指定 ENCODING 子句而 BYTE ORDER MARK 选项为 ON，则数据库服务器会在输入数据的开始位置搜索 BOM。如果找到 BOM，则会根据 BOM 的编码（UTF-16BE、UTF-16LE 或 UTF-8）自动选择源编码，同时不将 BOM 视作要装载的数据的一部分。
  - 如果未指定 ENCODING 子句且 BYTE ORDER MARK 选项为 OFF，或在输入数据的开始位置未找到 BOM，则使用数据库 CHAR 编码。

openxml 系统过程分析 xml-data 并将结果采用树的形式建模。每个元素、属性、文本节点或其它 XML 构造在树中都有单独的节点。提供给 openxml 系统过程的 XPath 查询用来从树中选择节点，然后所选择的节点被映射到结果集中。

openxml 系统过程所使用的 XML 分析器是非校验的，该分析器不读取外部 DTD 子集或外部参数实体。

当出现列表达式的多个匹配时，使用按照文档中的顺序（被分析之前的原始 XML 文档的顺序）的第一个匹配。如果没有匹配的节点，则返回 NULL。当选择了一个内部节点时，返回结果为内部节点的所有下级文本节点的连接值。

类型为 BINARY、LONG BINARY、IMAGE 和 VARBINARY 的列被假定采用 base64 编码格式并自动进行解码。如果使用 FOR XML 子句生成 XML，则上述类型的数据为 base64 编码并且可以使用 openxml 系统过程将其解码。

openxml 系统过程支持下列的 XPath 语法的子集：

完全支持 child、self、attribute、descendant、descendant-or-self 和 parent axes 语法。
在所有支持的功能中均可使用缩写的和未缩写形式的语法。例如，'a' 等效于 'child::a'，而 '..' 等效于 'parent::node()'。
在名称测试中可使用通配符。例如，'a/*/b'。
支持以下类型测试：node()、text()、processing-instruction() 和 comment()。
可使用 expr1[expr2] 和 expr1[expr2="string" ] 形式的限定符，其中 expr2 为所支持的任意 XPath 表达式。如果 expr2 匹配一个或多个节点，则限定符的值为 TRUE。例如，'a[b]' 查找至少具有一个 a 子项的 b 节点，而 a[b="I"] 查找至少具有一个文本值为 I 的 b 子项的 a 节点。

以下查询从作为 openxml 系统过程的第一个参数提供的 XML 文档中生成结果集：

SELECT * FROM openxml( '<products>
                 <ProductType ID="301">Tee Shirt</ProductType>
                 <ProductType ID="401">Baseball Cap</ProductType>
                 </products>',
                 '/products/ProductType' )
WITH ( ProductName LONG VARCHAR 'text()', ProductID CHAR(3) '@ID');

此查询会生成以下结果：

ProductName	ProductID
Tee Shirt	301
Baseball Cap	401

在以下示例中，第一个 <ProductType> 元素包含一个实体。当执行查询时，将此节点分析为具有四个子项的元素，其四个子项为：Tee、&、Sweater 和 Set。可使用 . 在结果集中将子项连接起来。

SELECT * FROM openxml( '<products>
                 <ProductType ID="301">Tee &amp; Sweater Set</ProductType>
                 <ProductType ID="401">Baseball Cap</ProductType>
                 </products>',
                 '/products/ProductType' )
WITH ( ProductName LONG VARCHAR '.', ProductID CHAR(3) '@ID');

此查询会生成以下结果：

ProductName	ProductID
Tee Shirt & Sweater Set	301
Baseball Cap	401

以下查询使用一个相等谓语来从所提供的 XML 文档中生成结果集。

SELECT * FROM openxml('<EmployeeDirectory>
   <Employee>
      <column name="EmployeeID">105</column>
      <column name="GivenName">Matthew</column>
      <column name="Surname">Cobb</column>
      <column name="Street">7 Pleasant Street</column>
      <column name="City">Grimsby</column>
      <column name="State">UT</column>
      <column name="PostalCode">02154</column>
      <column name="Phone">6175553840</column>
   </Employee>
  <Employee>
      <column name="EmployeeID">148</column>
      <column name="GivenName">Julie</column>
      <column name="Surname">Jordan</column>
      <column name="Street">1244 Great Plain Avenue</column>
      <column name="City">Woodbridge</column>
      <column name="State">AZ</column>
      <column name="PostalCode">01890</column>
      <column name="Phone">6175557835</column>
   </Employee>
  <Employee>
      <column name="EmployeeID">160</column>
      <column name="GivenName">Robert</column>
      <column name="Surname">Breault</column>
      <column name="Street">358 Cherry Street</column>
      <column name="City">Milton</column>
      <column name="State">PA</column>
      <column name="PostalCode">02186</column>
      <column name="Phone">6175553099</column>
   </Employee>
  <Employee>
      <column name="EmployeeID">243</column>
      <column name="GivenName">Natasha</column>
      <column name="Surname">Shishov</column>
      <column name="Street">151 Milk Street</column>
      <column name="City">Grimsby</column>
      <column name="State">UT</column>
      <column name="PostalCode">02154</column>
      <column name="Phone">6175552755</column>
   </Employee> 
</EmployeeDirectory>', '/EmployeeDirectory/Employee')
WITH ( EmployeeID INT 'column[@name="EmployeeID"]',
       GivenName    CHAR(20) 'column[@name="GivenName"]',
       Surname      CHAR(20) 'column[@name="Surname"]',
       PhoneNumber  CHAR(10) 'column[@name="Phone"]');

此查询会生成以下结果集：

EmployeeID	GivenName	Surname	PhoneNumber
105	Matthew	Cobb	6175553840
148	Julie	Jordan	6175557835
160	Robert	Breault	6175553099
243	Natasha	Shishov	6175552755

以下查询使用 XPath @attribute 表达式来生成结果集：

SELECT * FROM openxml( '<Employee
      EmployeeID="105"
      GivenName="Matthew"
      Surname="Cobb"
      Street="7 Pleasant Street"
      City="Grimsby"
      State="UT"
      PostalCode="02154"
      Phone="6175553840"
      />', '/Employee' )
WITH ( EmployeeID INT '@EmployeeID',
       GivenName    CHAR(20) '@GivenName',
       Surname      CHAR(20) '@Surname',
       PhoneNumber  CHAR(10) '@Phone');

以下查询操作的 XML 文档与上面查询中使用的 XML 文档类似，只是引入了 XML 命名空间。它演示了如何在 XPath 查询的名称测试中使用通配符，并生成与上面查询相同的结果集。

SELECT * FROM openxml( '<Employee  xmlns="http://www.iAnywhere.com/EmployeeDemo"
      EmployeeID="105"
      GivenName="Matthew"
      Surname="Cobb"
      Street="7 Pleasant Street"
      City="Grimsby"
      State="UT"
      PostalCode="02154"
      Phone="6175553840"
/>', '/*:Employee' )

WITH ( EmployeeID INT '@EmployeeID',
       GivenName    CHAR(20) '@GivenName',
       Surname      CHAR(20) '@Surname',
       PhoneNumber  CHAR(10) '@Phone');

或者，也可以指定命名空间声明：

SELECT * FROM openxml( '<Employee  xmlns="http://www.iAnywhere.com/EmployeeDemo"
      EmployeeID="105"
      GivenName="Matthew"
      Surname="Cobb"
      Street="7 Pleasant Street"
      City="Grimsby"
      State="UT"
      PostalCode="02154"
      Phone="6175553840"
/>', '/prefix:Employee', 1, '<r xmlns:prefix="http://www.iAnywhere.com/EmployeeDemo"/>' )
WITH ( EmployeeID INT '@EmployeeID',
       GivenName    CHAR(20) '@GivenName',
       Surname      CHAR(20) '@Surname',
       PhoneNumber  CHAR(10) '@Phone');

openxml 系统过程

语法 1

语法 2

参数

注释

另请参见

示例