xml 数据类型方法的使用准则Guidelines for Using xml Data Type Methods

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题介绍 xml 数据类型方法的使用指南 。This topic describes guidelines for using the xml data type methods.

PRINT 语句The PRINT Statement

xml 数据类型方法不能用于 PRINT 语句,如下面的示例所示 。The xml data type methods cannot be used in the PRINT statement as shown in the following example. xml 数据类型方法视为子查询来处理,而 PRINT 语句中不允许使用子查询 。The xml data type methods are treated as subqueries, and subqueries are not allowed in the PRINT statement. 因此,下面的示例将返回一个错误:As a result, the following example returns an error:

DECLARE @x xml
SET @x = '<root>Hello</root>'
PRINT @x.value('/root[1]', 'varchar(20)') -- will not work because this is treated as a subquery (select top 1 col from table)

一种解决方案是先将 value() 方法的结果分配给一个 xml 类型的变量,然后在查询中指定该变量。A solution is to first assign the result of the value() method to a variable of xml type and then specify the variable in the query.

DECLARE @x xml
DECLARE @c varchar(max)
SET @x = '<root>Hello</root>'
SET @c = @x.value('/root[1]', 'varchar(11)')


xml 数据类型方法在内部视为子查询来处理。The xml data type methods are treated internally as subqueries. 因为 GROUP BY 需要一个标量并且不允许使用聚合和子查询,所以在 GROUP BY 子句中不能指定 xml 数据类型方法。Because GROUP BY requires a scalar and does not allow aggregates and subqueries, you cannot specify the xml data type methods in the GROUP BY clause. 另一种解决方案是调用用户定义函数,然后在其内部使用 XML 方法。A solution is to call a user-defined function that uses XML methods inside of it.

报告错误Reporting Errors

报告错误时,xml 数据类型方法会引发一个如下格式的错误:When reporting errors, xml data type methods raise a single error in the following format:

Msg errorNumber, Level levelNumber, State stateNumber:
XQuery [database.table.method]: description_of_error

例如:For example:

Msg 2396, Level 16, State 1:
XQuery [xmldb_test.xmlcol.query()]: Attribute may not appear outside of an element

单一性检查Singleton Checks

如果编译器无法确定在运行时能否确保单一性,则具有单一性要求的位置步骤、函数参数和运算符将返回错误。Location steps, function parameters, and operators that require singletons will return an error if the compiler cannot determine whether a singleton is guaranteed at run time. 此问题经常出现在非类型化数据上。This problem occurs frequently with untyped data. 例如,查找属性时就需要使用单一的父元素。For example, the lookup of an attribute requires a singleton parent element. 通过一个用来选择单个父节点的序号即可满足此要求。An ordinal that selects a single parent node is sufficient. 而在计算 node() -value() 组合以提取属性值时可能不需要指定序号规范。The evaluation of a node()-value() combination to extract attribute values may not require the ordinal specification. 如下例所示。This is shown in the next example.

示例:已知单一性Example: Known Singleton

在此示例中,nodes() 方法为每个 <book> 元素生成一个单独的行。In this example, the nodes() method generates a separate row for each <book> element. 节点进行计算的 value() <book> 方法提取 @genre 值,并且是单一属性。The value() method that is evaluated on a <book> node extracts the value of @genre and, being an attribute, is a singleton.

SELECT nref.value('@genre', 'varchar(max)') LastName
FROM   T CROSS APPLY xCol.nodes('//book') AS R(nref)

XML 架构用于对类型化的 XML 进行类型检查。XML schema is used for type checking of typed XML. 如果将某个节点指定为 XML 架构中单一的节点,则编译器将使用该信息,并且不会发生任何错误。If a node is specified as a singleton in the XML schema, the compiler uses that information and no error occurs. 否则,需要使用一个用来选择单个节点的序号。Otherwise, an ordinal that selects a single node is required. 具体而言,使用 descendant-or-self 轴 (//) 轴(例如在 /book//title 中)会丢失 <title> 元素的单一性基数推理,即使 XML 架构指定其如此。In particular, the use of descendant-or-self axis (//) axis, such as in /book//title, looses singleton cardinality inference for the <title> element, even if the XML schema specifies it to be so. 因此,应将其重写为 (/book//title)[1]Therefore, you should rewrite it as (/book//title)[1].

对于类型检查,务必注意 //first-name[1](//first-name)[1] 之间的差异。It is important to remain aware of the difference between //first-name[1] and (//first-name)[1] for type checking. 前者返回一组 <first-name> 节点,其中每个节点都是其同级节点中最左侧的 <first-name> 节点。The former returns a sequence of <first-name> nodes in which each node is the leftmost <first-name> node among its siblings. 后者返回 XML 实例中按文档顺序排列的第一个单一的 <first-name> 节点。The latter returns the first singleton <first-name> node in document order in the XML instance.

示例:使用 value()Example: Using value()

下面对非类型化 XML 列的查询导致发生静态的编译错误。这是因为 value() 希望将一个单一节点作为第一个参数,而编译器无法确定在运行时是否将仅有一个 <last-name> 节点:The following query on an untyped XML column results in a static, compilation error.This is because value() expects a singleton node as the first argument and the compiler cannot determine whether only one <last-name> node will occur at run time:

SELECT xCol.value('//author/last-name', 'nvarchar(50)') LastName

可以考虑下面的解决办法:Following is a solution that you could consider:

SELECT xCol.value('//author/last-name[1]', 'nvarchar(50)') LastName

但是,该解决办法不解决错误,因为在每个 XML 实例中可能会有多个 <author> 节点。However, this solution does not solve the error, because multiple <author> nodes may occur in each XML instance. 采用下面的重写代码可以解决问题:The following rewrite works:

SELECT xCol.value('(//author/last-name/text())[1]', 'nvarchar(50)') LastName

此查询返回每个 XML 实例中第一个 <last-name> 元素的值。This query returns the value of the first <last-name> element in each XML instance.

另请参阅See Also