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

Question

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 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!

score 1 · Accepted Answer · answered Aug 05 '11 at 15:04

1

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

answered Aug 05 '11 at 15:04

Mikael Eriksson

Thanks again for the answer. Can you recommend a good book or Web site for learning how to use XQuery in SQL Server effectively? So far, I've found the MSDN materials extremely wordy, badly organized, and unnecessarily complicated. – Stavros Macrakis Aug 05 '11 at 16:27
@Stavros - Have a look at this answer. http://stackoverflow.com/questions/6749469/sql-xml-order-processor/6768891#6768891. It contains the links I use as reference. I do not know of any tutorial or book about XQuery in SQL Server. – Mikael Eriksson Aug 05 '11 at 21:24

1 Answers1