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.

Solution 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)')

Solution 2

declare @xml xml;
set @xml = '<Event><Username>BURGUNDY</Username></Event>'

Select @xml.value('/Event[1]/Username[1]', 'varchar(30)');

Solution 3

use .value() instead of .query()

Comments

[Daryl]

I have a query that returns many rows. Each row is an xml document. When I run [XMLContent].query() I only receive one value back when there should be a value for every row in my original select? Does that make sense?