Why is CROSS APPLY needed when using XPath queries?

The answer to your question is in your question.

The result of the nodes() method is a rowset

You can't do this either

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

But you can do

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

A straight SELECT ... FROM T can't add or subtract rows to the resultset no matter what functions you call in the SELECT list. That just isn't how SQL works.

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