MSSQL 2008 get all levels of item

Question

say I have xml in a SQL xml type field e.g.

    @x='<root>
         <item>
           <title></title>
           <item>
             <title></title>
           </item>
         </item>
       </root>'

How would I go about getting nth level items in a query?

Obviously to get the first level you would use;

    select
     t.p.query('.')
    from
     @x.nodes('/root/item') t(p)

and to get the next level as well you would add

    cross apply
         @x.nodes('/root/item/item')

but at runtime we do not know the depth the xml may go to.

Can anyone point me in the right direction.

Thanks!

Answer 1

If you want all item nodes you can do like this

select t.p.query('.')
from @x.nodes('//item') t(p)

Result:

(No column name)
<item><title /><item><title /></item></item>
<item><title /></item>

If you want only the innermost item node you can do like this

select
  t.p.query('.')
from @x.nodes('//item[count(item) = 0]') t(p)

Result:

(No column name)
<item><title /></item>