title: MSSQL 2008 get all levels of item
body: <p>say I have xml in a SQL xml type field e.g.</p>
  
  <pre><code>    @x='&lt;root&gt;
           &lt;item&gt;
             &lt;title&gt;&lt;/title&gt;
             &lt;item&gt;
               &lt;title&gt;&lt;/title&gt;
             &lt;/item&gt;
           &lt;/item&gt;
         &lt;/root&gt;'
  </code></pre>
  
  <p>How would I go about getting nth level items in a query?</p>
  
  <p>Obviously to get the first level you would use;</p>
  
  <pre><code>    select
       t.p.query('.')
      from
       @x.nodes('/root/item') t(p)
  </code></pre>
  
  <p>and to get the next level as well you would add </p>
  
  <pre><code>    cross apply
           @x.nodes('/root/item/item')
  </code></pre>
  
  <p>but at runtime we do not know the depth the xml may go to.</p>
  
  <p>Can anyone point me in the right direction.</p>
  
  <p>Thanks!</p>