sql server programming: /*
I am trying to extract a subset of nodes from multiple xml instances into
one instance, but I do now know the proper syntax. Can someone please help
me out? Finally starting to learn XQuery! See below for DDL and desired
result.

Thanks so much!!
~PJ
*/

create table XmlDataTable (
Instance xml
)
go
insert XmlDataTable ( Instance ) values ('<root><node>node 1</node></root>')
insert XmlDataTable ( Instance ) values ('<root><node>node 2</node></root>')
go


/*
I would like to know the proper XQuery Syntax to return this result
<root>
<node>node 1</node>
<node>node 2</node>
</root>
*/
-- this is not right as it returns multiple instances
select
instance.query('
<root>
{
for $r in /root
return $r/node
}
</root>')
from xmldatatable

drop table XmlDataTable

[quoted text, click to view]

I think you want this,

select instance.query('/root/node')
from xmlDatatable
for xml path(''), root('root')

I am as new to this as you.
But may be you can try this..

select cast((select instance.query('/root/node') from
XmlDataTable
for xml path('')) as XML) as 'root'

Works great! Love the FOR XML PATH...good bye FOR XML EXPLICIT!


[quoted text, click to view]