Using .query() in t-sql to get only inner text

Question

How can I use the .query() syntax of t-sql to select a specific node, but only get the inner text rather than the inner text wrapped in the node tags?

As in, when I do this:

SELECT TOP 1 [XMLContent].query('/Event/Username'), * from Events

I get:

<Username>BURGUNDY</Username>

But what I want is is just BURGUNDY. Obvious I could do some sub strings to get it, but I was hoping there was a quick and easy way to do it.

Answer 1

You could use the xquery data() function:

[XMLContent].query('data(/Event/Username)')

But, this will return XML as the type (even though there are no tags).

You could also use .value instead of .query:

[XMLContent].value('/Event[1]/Username[1]', 'NVARCHAR(MAX)')