How do I use an XQuery result list as a relational table in SQL Server 2008?

I have a table with an XML column:

declare @xmltest xml;
set @xmltest =  
 '<list> 
    <el> <x>1</x> <y>one</y> </el>
    <el> <x>2</x> <y>two</y> </el>
  </list>'

Using XQuery, I can extract all the x's:

 select @xmltest.query('/list/el/x')

Which returns

<x>1</x><x>2</x>

But how do I extract them 开发者_JS百科as a SQL table, with one x per row?

Even better, how do I extract them as an SQL table, with two columns, x and y so that I can then work on them with SQL?

Thanks!

You can use nodes() and value()

select 
  N.value('x[1]', 'int') as x,
  N.value('y[1]', 'varchar(10)') as y
from @xmltest.nodes('/list/el') as T(N)

Result:

x           y
----------- ----------
1           one
2           two