Почему CROSS APPLY необходим при использовании запросов XPath?

TL;DR

Почему нет:

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

работа?

До вопроса

Почти каждый ответ, который я видел (или получил) для использования запросов XPath в SQL Server, требует, чтобы вы присоединились к документу XML обратно к себе, используя CROSS APPLY.

Почему?

Например:

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)

Например:

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

Например:

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);

И даже из электронных книг MSDN:

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 Books Online) не объясняет, зачем он нужен, какую проблему он решает, что он делает или как он работает.

Даже самый простой случай нуждается в них

Даже самый простой пример использования 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
)

Теперь у нас есть таблица psuedo, на которую мы можем запросить:

Начнем с очевидного

Сначала мне нужны люди. В реальном 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>

... и теперь я устал. Я потратил три часа на то, чтобы написать этот вопрос, поверх четырех часов, которые я просил вчера задать вопрос. Я вернусь к этому вопросу позже; когда здесь прохладно, и у меня больше энергии, чтобы просить о помощи.

Второй ветер

Основная проблема заключается в том, что независимо от того, что я делаю, я получаю только одну возвращаемую строку. Я хочу вернуть три строки (потому что есть три человека). У SQL Server есть функция, которая может преобразовывать строки XML (называемые узлами) в строки SQL Server (называемые строками). Это функция .nodes:

Метод nodes() полезен, если вы хотите уничтожить экземпляр типа XML xml в реляционных данных. Он позволяет идентифицировать узлы, которые будут отображаться в новую строку.

Это означает, что вы вызываете метод .nodes с запросом XPath в типе данных xml. И то, что раньше возвращалось в 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, уровень 16, состояние 1, строка 8
Столбец "SomeOtherXmlColumn", который был возвращен методом nodes(), не может использоваться напрямую. Его можно использовать только с одним из четырех методов типа данных XML: exist(), nodes(), query() и value() или в IS NULL и IS NOT NULL.

Я предполагаю, что это связано с тем, что мне не разрешено просматривать набор результатов (т.е. * не допускается). Нет проблем. Я буду использовать тот же самый .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>

Это имеет смысл. Я ожидал, что запрос XPath в SQL Server будет вести себя как XPath. Но ретроспективное внимательное чтение документов говорит иначе:

Результатом метода nodes() является набор строк, содержащий логические копии исходных экземпляров XML. В этих логических копиях контекст node каждого экземпляра строки устанавливается в один из узлов, идентифицированных с выражением запроса, так что последующие запросы могут перемещаться относительно этих контекстных узлов.

Теперь сделайте это с столбцом `xml`

Предыдущий пример был для переменной типа xml. Теперь нам нужно модифицировать функцию .nodes для работы со таблицей, содержащей столбец xml:

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

Нет, это не работает:

Msg 227, уровень 15, состояние 1, строка 8
"nodes" не является допустимой функцией, свойством или полем.

Хотя .nodes является допустимым методом типа данных xml, он просто не работает, когда вы пытаетесь использовать его в типе данных xml. Он также не работает при использовании в типе xml:

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

Msg 208, уровень 16, состояние 1, строка 8
Недопустимое имя объекта "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.

Кроме того, я верю, что в этом есть нечто большее. На самом деле должно быть что-то вроде CROSS APPLY. Но я не вижу, где - или почему.

Ответ 1

Query:

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() возвращает rowset:

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

Ответ 2

Ответ на ваш вопрос в вашем вопросе.

Результатом метода nodes() является набор строк

Вы не можете сделать это либо

WITH T(X) AS
(
SELECT 1
)
SELECT X, (SELECT 'A' AS Y UNION ALL SELECT 'B' AS Y)
FROM T

Но вы можете сделать

WITH T(X) AS
(
SELECT 1
)
SELECT X, Y
FROM T
CROSS APPLY (SELECT 'A' AS Y UNION ALL SELECT 'B' AS Y) C

Прямой SELECT ... FROM T не может добавлять или вычитать строки в набор результатов независимо от того, какие функции вы вызываете в списке SELECT. Это не так, как работает SQL.

TL;DR

До вопроса

Даже самый простой случай нуждается в них

Что он на самом деле делает?

Начнем с очевидного

К именам

Второй ветер

Теперь сделайте это с столбцом xml

Ответ 1

Ответ 2

Теперь сделайте это с столбцом `xml`