T-SQL XML Query, how to seperate matching nodes into individual rows?

Question

I have a table that has a column full of XML like:

<parent>
   <child>
      <name>Sally</name>
   </child>
   <child>
      <name>Bobby</name>
   </child>
</parent>

I'm trying to extract all of the names of the children into seperate rows. My desired resultset would look like:

   Sally
   Bobby

However, if I do something like:

SELECT data.query('data(//parents/child/name)') FROM stuff

I get back a single row that looks like

Sally Bobby

What am I doing wrong with the XPath?

EDIT: Not the real schema or data, just an example. Also, I can't simply split on spaces.

Answer 1

I think you want to look into CROSS APPLY:

http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

Answer 2

Here you go:

create table #t (data xml)


insert #t values ('
<parent>
   <child>
      <name>Sally</name>
   </child>
   <child>
      <name>Bobby</name>
   </child>
</parent>
')


insert #t values ('
<parent>
   <child>
      <name>Fred</name>
   </child>
   <child>
      <name>Bill</name>
   </child>
</parent>
')


select C.* from #t 
cross apply (select name.value('name[1]', 'varchar(255)') as [Name] from data.nodes('//parent/child') as c(name)) as C

See: http://odata.stackexchange.com/stackoverflow/qt/4066/

​