I have a table with several rows. Each row holds an XML typed field 'descriptor'. A descriptor looks as:
<root>
<item name="a" />
<item name="b" />
</root>
In 'root' there can be several (0-100) 'item' node.
I want to select all the items' name from all table rows' descriptor at the same time (somehow like this):
create table #mytable (descriptor xml)
insert into #mytable (descriptor) values ('<root><item name="a"/><item name="b"/></root>')
insert into #mytable (descriptor) values ('<root><item name="c"/></root>')
SELECT p.value('./@name', 'nvarchar(100)')
FROM M.descriptor.nodes('/root/item') as p(p)
INNER JOIN #mytable M on 1=1
OPTION ( OPTIMIZE FOR ( M.descriptor = NULL ) )
But it does not work :( My lack of knowledge with .nodes() and other sql specific things (sorry)... Does it possible anyway? What is the right syntax? Thanks in advance!
You probably wanted to use CROSS APPLY:
create table #mytable (descriptor xml)
insert into #mytable (descriptor) values ('<root><item name="a"/><item name="b"/></root>')
insert into #mytable (descriptor) values ('<root><item name="c"/></root>')
SELECT p.value('./@name', 'nvarchar(100)')
FROM #mytable M
CROSS APPLY M.descriptor.nodes('/root/item') as p(p);