getting a set of values from xml

i have imported xml into an xml datatype variable. here is a tiny version of my xml file.

<Root>

<TOP>

<USERS>

<USER>

<USER>

<USERNAME>jukkaw</USERNAME>

</USER>

<USER>

<USERNAME>v-derekn</USERNAME>

</USER>

</USERS>

</TOP>

</Root>'

I need to pullout just the username, so the query method is out as it will return it in xml format. how do i just get a column containing all of the usernames?

You could use nodes table-value function:

create table #xml_table

(

xml_col xml

)

go

insert into #xml_table values('<Root>

<TOP>

<USERS>

<USER>

<USERNAME>jukkaw</USERNAME>

</USER>

<USER>

<USERNAME>v-derekn</USERNAME>

</USER>

</USERS>

</TOP>

</Root>

')

select x.value('.[1]','varchar(100)')

from #xml_table t cross apply xml_col.nodes('/Root/TOP/USERS/USER/USERNAME/text()') as tab(x)