sql-server - 为什么在使用 XPath 查询时需要 CROSS APPLY？

Question

tl;博士

为什么不:

SELECT 
    SomeXmlColumn.nodes('/people/person') AS foo(b)
FROM MyTable

工作？

之前的问题

我几乎见过(或得到)在 SQL Server 中使用 XPath 查询的答案要求您使用 CROSS APPLY 将 XML 文档连接回自身。 .

为什么？

For example:

SELECT 
   p.value('(./firstName)[1]', 'VARCHAR(8000)') AS firstName,
   p.value('(./lastName)[1]', 'VARCHAR(8000)') AS lastName
FROM table 
   CROSS APPLY field.nodes('/person') t(p)

For example :

SELECT a.BatchXml.value('(Name)[1]', 'varchar(50)') AS Name,
    a.BatchXml.value('(IDInfo/IDType)[1]', 'varchar(50)') AS IDType,
    a.BatchXml.value('(IDInfo/IDOtherDescription)[1]', 'varchar(50)') AS IDOtherDescription
FROM BatchReports b
CROSS APPLY b.BatchFileXml.nodes('Customer') A(BatchXml)
WHERE a.BatchXml.exist('IDInfo/IDType[text()=3]')=1

For example :

SELECT  b.BatchID,
        x.XmlCol.value('(ReportHeader/OrganizationReportReferenceIdentifier)[1]','VARCHAR(100)') AS OrganizationReportReferenceIdentifier,
        x.XmlCol.value('(ReportHeader/OrganizationNumber)[1]','VARCHAR(100)') AS OrganizationNumber
FROM    Batches b
CROSS APPLY b.RawXml.nodes('/CasinoDisbursementReportXmlFile/CasinoDisbursementReport') x(XmlCol);

And even from MSDN Books Online:

SELECT nref.value('first-name[1]', 'nvarchar(32)') FirstName,
       nref.value('last-name[1]', 'nvarchar(32)') LastName
FROM    [XmlFile] CROSS APPLY [Contents].nodes('//author') AS p(nref)

他们都使用它。但是没有人(甚至 SQL Server 联机丛书也不行)解释为什么需要它，它解决了什么问题，它在做什么，或者它是如何工作的。

即使是最简单的情况也需要它们

即使是采用 XML 的最简单示例:

<people>
   <person><firstName>Jon</firstName><lastName>Johnson</lastName></person>
   <person><firstName>Kathy</firstName><lastName>Carter</lastName></person>
   <person><firstName>Bob</firstName><lastName>Burns</lastName></person>
</people>

并返回值:

FirstName  LastName
=========  ========
Jon        Johnson
Kathy      Carter
Bob        Burns

需要加入:

SELECT 
   p.value('(./firstName)[1]', 'VARCHAR(8000)') AS firstName,
   p.value('(./lastName)[1]', 'VARCHAR(8000)') AS lastName
FROM table 
   CROSS APPLY field.nodes('/person') t(p)

令人困惑的是它甚至不使用它连接的表，为什么需要它？

由于对 XML 的查询从未被记录或解释过，希望我们现在可以解决这个问题。

它实际上有什么作用？

所以让我们从一个实际的例子开始，因为我们想要一个实际的答案，它给出了一个实际的解释:

DECLARE @xml xml;
SET @xml = 
'<people>
   <person><firstName>Jon</firstName><lastName>Johnson</lastName></person>
   <person><firstName>Kathy</firstName><lastName>Carter</lastName></person>
   <person><firstName>Bob</firstName><lastName>Burns</lastName></person>
</people>';
;WITH MyTable AS (
    SELECT @xml AS SomeXmlColumn
)

现在我们有了可以查询的伪表:



让我们从显而易见的开始

首先我需要人。在 真实 XML，我可以轻松返回三行:

/people/person

这给出了 NodeList包含三个节点:

<person><firstName>Jon</firstName><lastName>Johnson</lastName></person>
<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>
<person><firstName>Bob</firstName><lastName>Burns</lastName></person>

在 SQL Server 中，相同的查询:

SELECT 
   SomeXmlColumn.query('/people/person')
FROM MyTable

不返回三行，而是一行，其中包含三个节点的 XML:

<person>
  <firstName>Jon</firstName>
  <lastName>Johnson</lastName>
</person>
<person>
  <firstName>Kathy</firstName>
  <lastName>Carter</lastName>
</person>
<person>
  <firstName>Bob</firstName>
  <lastName>Burns</lastName>
</person>

显然这是不合适的，当我的最终目标是返回 3 行 .我不知何故必须将三行分成三行。

上的名字

我的实际目标是获得 firstName和 lastName .在 XPath 中，我可以执行以下操作:

/people/person/firstName|/people/person/lastName

这让我得到了六个节点，尽管它们不相邻

<firstName>Jon</firstName>
<lastName>Johnson</lastName>
<firstName>Kathy</firstName>
<lastName>Carter</lastName>
<firstName>Bob</firstName>
<lastName>Burns</lastName>

在 SQL Server 中，我们尝试类似的东西

SELECT 
    SomeXmlColumn.query('/people/person/firstName') AS FirstName,
    SomeXmlColumn.query('/people/person/lastName') AS LastName
FROM MyTable

这给了我们一个 行 ，每列包含一个 XML 片段:

FirstName                     LastName
============================  ============================
<firstName>Jon</firstName>    <lastName>Johnson</lastName>
<firstName>Kathy</firstName>  <lastName>Carter</lastName>
<firstName>Bob</firstName>    <lastName>Burns</lastName>

……现在我累了。我花了三个小时写这个问题，在 four hours I spent asking yesterday's question 之上.稍后我会回到这个问题；等这里凉快了，我就有更多的力气求救了。

二次风

根本问题是，无论我做什么，我总是只返回一行。我想要返回三行(因为有三个人)。 SQL Server 确实有一个函数可以将 XML 行(称为节点)转换为 SQL Server 行(称为行)。这是 .nodes功能:

The nodes() method is useful when you want to shred an xml data type instance into relational data. It allows you to identify nodes that will be mapped into a new row.

这意味着您“调用”了 .nodes在 xml 上使用 XPath 查询的方法数据类型。过去在 SQL Server 中作为具有三个节点的一行返回的内容(正确地)作为三个节点返回:

.nodes('/people/person') AS MyDerivedTable(SomeOtherXmlColumn)

从概念上讲，这将返回:

SomeOtherXmlColumn
------------------------------------------------------------------------
<person><firstName>Jon</firstName><lastName>Johnson</lastName></person>
<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>
<person><firstName>Bob</firstName><lastName>Burns</lastName></person>

但如果你真的尝试使用它，它不起作用:

DECLARE @xml xml;
SET @xml = 
'<people>
   <person><firstName>Jon</firstName><lastName>Johnson</lastName></person>
   <person><firstName>Kathy</firstName><lastName>Carter</lastName></person>
   <person><firstName>Bob</firstName><lastName>Burns</lastName></person>
</people>';
SELECT *
FROM @xml.nodes('/people/person') AS MyDervicedTable(SomeOtherXmlColumn)

给出错误:

Msg 493, Level 16, State 1, Line 8
The column 'SomeOtherXmlColumn' that was returned from the nodes() method cannot be used directly. It can only be used with one of the four XML data type methods, exist(), nodes(), query(), and value(), or in IS NULL and IS NOT NULL checks.

我认为这是因为我不允许查看结果集(即不允许使用 *)。没问题。我将使用相同的 .query我最初使用的是:

SELECT SomeOtherXmlColumn.query('/') AS SomeOtherOtherXmlColumn
FROM @xml.nodes('/people/person') AS MyDervicedTable(SomeOtherXmlColumn)

返回行。但它不是将节点列表拆分为行，而是复制整个 XML:

SomeOtherOtherXmlColumn
----------------------------------------
<people><person><firstName>Jon</firstName><lastName>Johnson</lastName></person><person><firstName>Kathy</firstName><lastName>Carter</lastName></person><person><firstName>Bob</firstName><lastName>Burns</lastName></person></people>
<people><person><firstName>Jon</firstName><lastName>Johnson</lastName></person><person><firstName>Kathy</firstName><lastName>Carter</lastName></person><person><firstName>Bob</firstName><lastName>Burns</lastName></person></people>
<people><person><firstName>Jon</firstName><lastName>Johnson</lastName></person><person><firstName>Kathy</firstName><lastName>Carter</lastName></person><person><firstName>Bob</firstName><lastName>Burns</lastName></person></people>

这是有道理的。我期望 SQL Server 中的 XPath 查询表现得像 XPath。但事后仔细阅读文档的情况并非如此:

The result of the nodes() method is a rowset that contains logical copies of the original XML instances. In these logical copies, the context node of every row instance is set to one of the nodes identified with the query expression, so that subsequent queries can navigate relative to these context nodes.

现在用 xml 来做柱子

前面的例子是针对 xml 类型的变量。 .现在我们必须改造 .nodes用于处理包含 xml 的表的函数柱子:

SELECT 
   SomeXmlColumn.nodes('/people/person')
FROM MyTable

不，这不起作用:

Msg 227, Level 15, State 1, Line 8
"nodes" is not a valid function, property, or field.

虽然 .nodes是 xml 的有效方法数据类型，当您尝试在 xml 上使用它时它根本不起作用数据类型。在 xml 上使用时也不起作用数据类型:

SELECT *
FROM MyTable.SomeXmlColumn.nodes('/people/person')

Msg 208, Level 16, State 1, Line 8
Invalid object name 'MyTable.SomeXmlColumn.nodes'.

我认为这就是为什么 CROSS APPLY需要修改器。不是因为你加入了任何东西，而是因为 SQL Server 解析器将拒绝识别 .nodes除非它前面有关键字 cross apply :

SELECT 
    'test' AS SomeTestColumn
FROM MyTable CROSS APPLY MyTable.SomeXmlColumn.nodes('/people/person') AS MyDerivedTable(SomeOtherXmlColumn)

我们开始到达某个地方:

SomeTestColumn
--------------
test
test
test

因此，如果我们想查看返回的 XML:

SELECT 
    SomeOtherXmlColumn.query('/')
FROM (MyTable CROSS APPLY MyTable.SomeXmlColumn.nodes('/people/person') AS MyDerivedTable(SomeOtherXmlColumn))

现在我们有三行。

看来 cross apply不用于连接，而只是允许 .nodes 的关键字上类

并且似乎 SQL Server 优化器只是拒绝接受任何使用

.nodes

你必须实际使用:

CROSS APPLY .nodes

事情就是这样。如果是这样的话 - 那很好。这就是规则。这导致了多年的困惑；以为我在用 cross apply 加入其他东西运算符(operator)。

除了我相信还有更多。不知何故，实际上必须有一个 cross apply发生。但我看不到在哪里 - 或者为什么。

Answer 1

询问:

SELECT x.i.value('(./text())[1]', 'VARCHAR(10)')
FROM MyTable.SomeXmlColumn.nodes('./people/person/firstName') AS x(i);

不起作用，原因与此查询不起作用的原因相同:

SELECT *
FROM Person.Person.FirstName;

但这确实:

SELECT FirstName
FROM Person.Person;

——

FROM 子句需要行集，所以这是有效的，因为 nodes() 返回行集:

DECLARE @xml AS XML = 
'<people>
   <person><firstName>Jon</firstName><lastName>Johnson</lastName></person>
   <person><firstName>Kathy</firstName><lastName>Carter</lastName></person>
   <person><firstName>Bob</firstName><lastName>Burns</lastName></person>
</people>';

SELECT x.i.value('(./text())[1]', 'VARCHAR(10)')
FROM @xml.nodes('./people/person/firstName') AS x(i);

如果 xml 不是变量而是表中的值，我们首先需要从这个值中提取行，这就是 CROSS APPLY 派上用场的时候:

SELECT x.i.value('(./text())[1]', 'VARCHAR(10)')
FROM MyTable as t
CROSS APPLY 
   t.SomeXmlColumn.nodes('./people/person/firstName') AS x(i);

CROSS APPLY 运算符将右表达式应用于左表 (MyTable) 中的每条记录。

在 MyTable 表中有一个包含 xml 的记录。

CROSS APPLY 获取此记录并将其公开给右侧的表达式。

右表达式使用 nodes() 函数提取记录。

结果有 1 x 3 = 3 个记录(xml 节点)，然后由 SELECT 子句处理。

与“正常”的 CROSS APPLY 查询相比:

SELECT c.CustomerID, soh.TotalDue, soh.OrderDate
FROM Sales.Customer AS c
CROSS APPLY
    (SELECT TOP(2) TotalDue, OrderDate
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = c.CustomerID
ORDER BY TotalDue DESC) AS soh;

c.CustomerID 是我们的 t.SomeXmlColumn