Why is CROSS APPLY needed when using XPath queries?

Question

Query:

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

doesn’t work, for the same reason why this query doesn’t work:

SELECT *
FROM Person.Person.FirstName;

but this does:

SELECT FirstName
FROM Person.Person;

–

FROM clause expects rowset, so this is valid, since nodes() returns 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);

If xml is not a variable but value in table, we first need to extract rows from this value, and this is when CROSS APPLY comes in handy:

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 operator applies the right expression to each record from the left table (MyTable).

In MyTable table there is one record containing xml.
CROSS APPLY fetches this record and exposes it to expression in the right.
Right expression extracts records using nodes() function.
As a result there are 1 x 3 = 3 records (xml nodes) which are then processed by SELECT clause.

Compare to ‘normal’ CROSS APPLY query:

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 is our t.SomeXmlColumn

Leave a Comment Cancel reply