title: Why is CROSS APPLY needed when using XPath queries?
body: <h1>tl;dr</h1>
  
  <p>Why doesn't:</p>
  
  <pre><code>SELECT 
      SomeXmlColumn.nodes('/people/person') AS foo(b)
  FROM MyTable
  </code></pre>
  
  <p>work?</p>
  
  <h1>The Before Question</h1>
  
  <p>Nearly ever answer I've seen (or gotten) for using XPath queries in SQL Server requires that you join the XML document back to itself using a <code>CROSS APPLY</code>.</p>
  
  <p>Why?</p>
  
  <p><a href="https://stackoverflow.com/a/899382/12597">For example:</a></p>
  
  <pre><code>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)
  </code></pre>
  
  <p><a href="https://stackoverflow.com/a/23483596/12597">For example</a>:</p>
  
  <pre><code>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
  </code></pre>
  
  <p><a href="https://stackoverflow.com/a/14713431/12597">For example</a>:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p><a href="http://msdn.microsoft.com/en-us/library/ms345118%28SQL.90%29.aspx" rel="noreferrer">And even from MSDN Books Online:</a></p>
  
  <pre><code>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)
  </code></pre>
  
  <p>They all use it. But nobody (not even the SQL Server Books Online) explains why it's needed, what problem it solves, what it's doing, or how it works.</p>
  
  <h2>Even the simplest case needs them</h2>
  
  <p>Even the simplest example of taking the XML:</p>
  
  <pre><code>&lt;people&gt;
     &lt;person&gt;&lt;firstName&gt;Jon&lt;/firstName&gt;&lt;lastName&gt;Johnson&lt;/lastName&gt;&lt;/person&gt;
     &lt;person&gt;&lt;firstName&gt;Kathy&lt;/firstName&gt;&lt;lastName&gt;Carter&lt;/lastName&gt;&lt;/person&gt;
     &lt;person&gt;&lt;firstName&gt;Bob&lt;/firstName&gt;&lt;lastName&gt;Burns&lt;/lastName&gt;&lt;/person&gt;
  &lt;/people&gt;
  </code></pre>
  
  <p>and returning the values:</p>
  
  <pre><code>FirstName  LastName
  =========  ========
  Jon        Johnson
  Kathy      Carter
  Bob        Burns
  </code></pre>
  
  <p>needs a join:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>What's confusing is that it doesn't even <em>use</em> the table it joins from, why does it need it?</p>
  
  <p>Since querying for XML has never been documented or explained, hopefully we can solve that now.</p>
  
  <h1>What does it actually do?</h1>
  
  <p>So let's start with an actual example, since we want an actual answer, that gives an actual explanation:</p>
  
  <pre><code>DECLARE @xml xml;
  SET @xml = 
  '&lt;people&gt;
     &lt;person&gt;&lt;firstName&gt;Jon&lt;/firstName&gt;&lt;lastName&gt;Johnson&lt;/lastName&gt;&lt;/person&gt;
     &lt;person&gt;&lt;firstName&gt;Kathy&lt;/firstName&gt;&lt;lastName&gt;Carter&lt;/lastName&gt;&lt;/person&gt;
     &lt;person&gt;&lt;firstName&gt;Bob&lt;/firstName&gt;&lt;lastName&gt;Burns&lt;/lastName&gt;&lt;/person&gt;
  &lt;/people&gt;';
  ;WITH MyTable AS (
      SELECT @xml AS SomeXmlColumn
  )
  </code></pre>
  
  <p>Now we have <em>psuedo</em> table we can query from:</p>
  
  <p><img src="https://i.stack.imgur.com/G5VDf.png" alt="enter image description here"></p>
  
  <h1>Let's start with the obvious</h1>
  
  <p>First I need the people. In <strong>real</strong> XML, I can easily return the three rows:</p>
  
  <pre><code>/people/person
  </code></pre>
  
  <p>Which gives a <code>NodeList</code> containing three nodes:</p>
  
  <pre><code>&lt;person&gt;&lt;firstName&gt;Jon&lt;/firstName&gt;&lt;lastName&gt;Johnson&lt;/lastName&gt;&lt;/person&gt;
  &lt;person&gt;&lt;firstName&gt;Kathy&lt;/firstName&gt;&lt;lastName&gt;Carter&lt;/lastName&gt;&lt;/person&gt;
  &lt;person&gt;&lt;firstName&gt;Bob&lt;/firstName&gt;&lt;lastName&gt;Burns&lt;/lastName&gt;&lt;/person&gt;
  </code></pre>
  
  <p>In SQL Server, the same query:</p>
  
  <pre><code>SELECT 
     SomeXmlColumn.query('/people/person')
  FROM MyTable
  </code></pre>
  
  <p>doesn't return three rows, but rather one row with the XML containing the three nodes:</p>
  
  <pre><code>&lt;person&gt;
    &lt;firstName&gt;Jon&lt;/firstName&gt;
    &lt;lastName&gt;Johnson&lt;/lastName&gt;
  &lt;/person&gt;
  &lt;person&gt;
    &lt;firstName&gt;Kathy&lt;/firstName&gt;
    &lt;lastName&gt;Carter&lt;/lastName&gt;
  &lt;/person&gt;
  &lt;person&gt;
    &lt;firstName&gt;Bob&lt;/firstName&gt;
    &lt;lastName&gt;Burns&lt;/lastName&gt;
  &lt;/person&gt;
  </code></pre>
  
  <p>Obviously this is unsuitable, when my end goal is to return 3 <strong>rows</strong>. I somehow have to break up the three rows into three rows.</p>
  
  <h1>Onto the names</h1>
  
  <p>My actual goal is to get the <code>firstName</code> and <code>lastName</code>. In XPath I could do something like:</p>
  
  <pre><code>/people/person/firstName|/people/person/lastName
  </code></pre>
  
  <p>which gets me the six nodes, although they are not adjoining</p>
  
  <pre><code>&lt;firstName&gt;Jon&lt;/firstName&gt;
  &lt;lastName&gt;Johnson&lt;/lastName&gt;
  &lt;firstName&gt;Kathy&lt;/firstName&gt;
  &lt;lastName&gt;Carter&lt;/lastName&gt;
  &lt;firstName&gt;Bob&lt;/firstName&gt;
  &lt;lastName&gt;Burns&lt;/lastName&gt;
  </code></pre>
  
  <p>In SQL Server, we try something similar</p>
  
  <pre><code>SELECT 
      SomeXmlColumn.query('/people/person/firstName') AS FirstName,
      SomeXmlColumn.query('/people/person/lastName') AS LastName
  FROM MyTable
  </code></pre>
  
  <p>which gets us one <strong>row</strong>, with each column containing an XML fragment:</p>
  
  <pre><code>FirstName                     LastName
  ============================  ============================
  &lt;firstName&gt;Jon&lt;/firstName&gt;    &lt;lastName&gt;Johnson&lt;/lastName&gt;
  &lt;firstName&gt;Kathy&lt;/firstName&gt;  &lt;lastName&gt;Carter&lt;/lastName&gt;
  &lt;firstName&gt;Bob&lt;/firstName&gt;    &lt;lastName&gt;Burns&lt;/lastName&gt;
  </code></pre>
  
  <p>...and now I'm tired. I've spent three hours writing this question, on top of the <a href="https://stackoverflow.com/questions/23483352/how-to-return-the-value-that-exists-in-sql-server-xpath/23483596?noredirect=1#comment36031482_23483596">four hours I spent asking yesterday's question</a>. I'll come back to this question later; when it's cooler in here, and I have more energy to beg for help.</p>
  
  <h1>Second wind</h1>
  
  <p>The fundamental problem is that no matter what I do, I keep getting only one row returned. I want three rows returned (because there are three people). SQL Server <em>does</em> have a function that can convert XML rows (called nodes) into SQL Server rows (called rows). It's the <code>.nodes</code> function:</p>
  
  <blockquote>
    <p>The <strong>nodes()</strong> 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.</p>
  </blockquote>
  
  <p>This means that you "call" the <code>.nodes</code> method with an XPath query on an <code>xml</code> data type. And what used to come back in SQL Server as one row with three nodes, comes back (correctly) as three nodes:</p>
  
  <pre><code>.nodes('/people/person') AS MyDerivedTable(SomeOtherXmlColumn)
  </code></pre>
  
  <p>Conceptually this returns:</p>
  
  <pre><code>SomeOtherXmlColumn
  ------------------------------------------------------------------------
  &lt;person&gt;&lt;firstName&gt;Jon&lt;/firstName&gt;&lt;lastName&gt;Johnson&lt;/lastName&gt;&lt;/person&gt;
  &lt;person&gt;&lt;firstName&gt;Kathy&lt;/firstName&gt;&lt;lastName&gt;Carter&lt;/lastName&gt;&lt;/person&gt;
  &lt;person&gt;&lt;firstName&gt;Bob&lt;/firstName&gt;&lt;lastName&gt;Burns&lt;/lastName&gt;&lt;/person&gt;
  </code></pre>
  
  <p>But if you <em>actually</em> try to use it, it doesn't work:</p>
  
  <pre><code>DECLARE @xml xml;
  SET @xml = 
  '&lt;people&gt;
     &lt;person&gt;&lt;firstName&gt;Jon&lt;/firstName&gt;&lt;lastName&gt;Johnson&lt;/lastName&gt;&lt;/person&gt;
     &lt;person&gt;&lt;firstName&gt;Kathy&lt;/firstName&gt;&lt;lastName&gt;Carter&lt;/lastName&gt;&lt;/person&gt;
     &lt;person&gt;&lt;firstName&gt;Bob&lt;/firstName&gt;&lt;lastName&gt;Burns&lt;/lastName&gt;&lt;/person&gt;
  &lt;/people&gt;';
  SELECT *
  FROM @xml.nodes('/people/person') AS MyDervicedTable(SomeOtherXmlColumn)
  </code></pre>
  
  <p>Gives the error:</p>
  
  <blockquote>
    <p>Msg 493, Level 16, State 1, Line 8<br>
    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.</p>
  </blockquote>
  
  <p>I presume this is because I'm not allowed to look at the results set (i.e. the <code>*</code> is not allowed). No problem. I'll use the same <code>.query</code> I used originally:</p>
  
  <pre><code>SELECT SomeOtherXmlColumn.query('/') AS SomeOtherOtherXmlColumn
  FROM @xml.nodes('/people/person') AS MyDervicedTable(SomeOtherXmlColumn)
  </code></pre>
  
  <p>Which returns rows. But rather than splitting a list of nodes into rows, it just duplicates the entire XML:</p>
  
  <pre><code>SomeOtherOtherXmlColumn
  ----------------------------------------
  &lt;people&gt;&lt;person&gt;&lt;firstName&gt;Jon&lt;/firstName&gt;&lt;lastName&gt;Johnson&lt;/lastName&gt;&lt;/person&gt;&lt;person&gt;&lt;firstName&gt;Kathy&lt;/firstName&gt;&lt;lastName&gt;Carter&lt;/lastName&gt;&lt;/person&gt;&lt;person&gt;&lt;firstName&gt;Bob&lt;/firstName&gt;&lt;lastName&gt;Burns&lt;/lastName&gt;&lt;/person&gt;&lt;/people&gt;
  &lt;people&gt;&lt;person&gt;&lt;firstName&gt;Jon&lt;/firstName&gt;&lt;lastName&gt;Johnson&lt;/lastName&gt;&lt;/person&gt;&lt;person&gt;&lt;firstName&gt;Kathy&lt;/firstName&gt;&lt;lastName&gt;Carter&lt;/lastName&gt;&lt;/person&gt;&lt;person&gt;&lt;firstName&gt;Bob&lt;/firstName&gt;&lt;lastName&gt;Burns&lt;/lastName&gt;&lt;/person&gt;&lt;/people&gt;
  &lt;people&gt;&lt;person&gt;&lt;firstName&gt;Jon&lt;/firstName&gt;&lt;lastName&gt;Johnson&lt;/lastName&gt;&lt;/person&gt;&lt;person&gt;&lt;firstName&gt;Kathy&lt;/firstName&gt;&lt;lastName&gt;Carter&lt;/lastName&gt;&lt;/person&gt;&lt;person&gt;&lt;firstName&gt;Bob&lt;/firstName&gt;&lt;lastName&gt;Burns&lt;/lastName&gt;&lt;/person&gt;&lt;/people&gt;
  </code></pre>
  
  <p>Which makes sense. I was <em>expecting</em> an XPath query in SQL Server to behave like XPath. But a hindsight careful reading of the docs say otherwise:</p>
  
  <blockquote>
    <p>The result of the <strong>nodes()</strong> 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.</p>
  </blockquote>
  
  <h1>Now do it with an <code>xml</code> column</h1>
  
  <p>The preceding example was for a variable of type <code>xml</code>. Now we have to retrofit the <code>.nodes</code> function to work with a table containing an <code>xml</code> column:</p>
  
  <pre><code>SELECT 
     SomeXmlColumn.nodes('/people/person')
  FROM MyTable
  </code></pre>
  
  <p>No, that doesn't work:</p>
  
  <blockquote>
    <p>Msg 227, Level 15, State 1, Line 8<br>
    "nodes" is not a valid function, property, or field.</p>
  </blockquote>
  
  <p>Although <code>.nodes</code> <em>is</em> a valid method of an <code>xml</code> data type, it simply doesn't work when you try to use it on an <code>xml</code> data type. Nor does it work on when used on an <code>xml</code> data type:</p>
  
  <pre><code>SELECT *
  FROM MyTable.SomeXmlColumn.nodes('/people/person')
  </code></pre>
  
  <blockquote>
    <p>Msg 208, Level 16, State 1, Line 8<br>
    Invalid object name 'MyTable.SomeXmlColumn.nodes'.</p>
  </blockquote>
  
  <p>Which I presume is why the <code>CROSS APPLY</code> modifier is needed. Not because you are joining anything, but because the SQL Server parser will refuse to recognize <code>.nodes</code> unless it's preceded with the keywords <code>cross apply</code>:</p>
  
  <pre><code>SELECT 
      'test' AS SomeTestColumn
  FROM MyTable CROSS APPLY MyTable.SomeXmlColumn.nodes('/people/person') AS MyDerivedTable(SomeOtherXmlColumn)
  </code></pre>
  
  <p>And we start to get somewhere:</p>
  
  <pre><code>SomeTestColumn
  --------------
  test
  test
  test
  </code></pre>
  
  <p>And so if we then want to see the XML that comes back:</p>
  
  <pre><code>SELECT 
      SomeOtherXmlColumn.query('/')
  FROM (MyTable CROSS APPLY MyTable.SomeXmlColumn.nodes('/people/person') AS MyDerivedTable(SomeOtherXmlColumn))
  </code></pre>
  
  <p>Now we have three rows.</p>
  
  <p>It seems that <code>cross apply</code> isn't used to a join, but merely a keyword that allows <code>.nodes</code> to work </p>
  
  <p>And it seems that the SQL Server optimizer just refuses to accept any use of</p>
  
  <pre><code>.nodes
  </code></pre>
  
  <p>and you must actually use:</p>
  
  <pre><code>CROSS APPLY .nodes
  </code></pre>
  
  <p>And that's just how it is. And if that's the case - that's fine. That's the rule. And that led to years of confusion; thinking I was joining something to something else with the <code>cross apply</code> operator.</p>
  
  <p>Except I believe there is more to it than that. There must, somehow, actually be a <code>cross apply</code> happening. But I cannot see where - or why.</p>