views:

363

answers:

1

In an XmlData column in SQL Server 2008 that has no schema assigned to it, how can I pull the first item at a particular node level? For example, I have:

SELECT 
XmlData.value('//*/*[1]','NVARCHAR(6)')
FROM table
where XmlData.Exist('//*/*[1]') = 1

I assume this does not work because if there are multiple nodes with different names at the 2nd level, the first of each of those could be returned (and the value() requires that a singleton be selected. Since I don't know what the names of any nodes will be, is there a way to always select whatever the first node is at the 2nd level?

+3  A: 

I found the answer by chaining Xquery .query() and .value()

XMLDATA.query('//*/*[1]').value('.[1]','NVARCHAR(6)')

This returns the value of the first node and works perfectly for my needs.

kd7iwp