列名为 XPath 节点测试的列

如果列名是某个 XPath 节点测试，将如下表所示映射该列的内容。 如果列名是某个 XPath 节点测试，则该列的内容将映射到相应的节点。 如果该列的 SQL 类型是 xml，将返回一个错误。

列名	行为
text()	对于名为 text() 的列，该列中的字符串值将被添加为文本节点。
comment()	对于名为 comment() 的列，该列中的字符串值将被添加为 XML 注释。
node()	对于名为 node() 的列，结果与列名为通配符 (*) 时相同。
处理指令（名称）	如果列名为处理指令，该列中的字符串值将被添加为此处理指令目标名称的 PI 值。

以下查询显示用作列名的节点测试。 它在得到的 XML 中添加文本节点和注释。

USE AdventureWorks2012;
GO
SELECT E.BusinessEntityID "@EmpID", 
        'Example of using node tests such as text(), comment(), processing-instruction()'                as "comment()",
        'Some PI'                   as "processing-instruction(PI)",
        'Employee name and address data' as "text()",
        'middle name is optional'        as "EmpName/text()",
        FirstName                        as "EmpName/First", 
        MiddleName                       as "EmpName/Middle", 
        LastName                         as "EmpName/Last",
        AddressLine1                     as "Address/AddrLine1",
        AddressLine2                     as "Address/AddrLIne2",
        City                             as "Address/City"
FROM   HumanResources.Employee AS E
INNER JOIN Person.Person AS P 
    ON P.BusinessEntityID = E.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress AS BAE
    ON BAE.BusinessEntityID = E.BusinessEntityID
INNER JOIN Person.Address AS A
    ON BAE.AddressID = A.AddressID
WHERE  E.BusinessEntityID=1
FOR XML PATH;

结果如下：

<row EmpID="1">

<!--Example of using node tests such as text(), comment(), processing-instruction() -->

<?PI Some PI?>

Employee name and address data

<EmpName>middle name is optional

<First>Ken</First>

<Last>Sánchez</Last>

</EmpName>

<Address>

<AddrLine1>4350 Minute Dr.</AddrLine1>

<City>Minneapolis</City>

</Address>

</row>

请参阅

概念

将 PATH 模式与 FOR XML 一起使用