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

Question

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

As in the case when I do this:

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

I get:

 <Username>BURGUNDY</Username> 

But I want only BURGUNDY . Obviously, I could make some substrings to get it, but I was hoping there was a quick and easy way to do this.

Answer 1

You can use the xquery data () function:

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

But this will return XML as a type (even if there are no tags).

You can also use .value instead of .query:

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

Answer 2

 declare @xml xml; set @xml = '<Event><Username>BURGUNDY</Username></Event>' Select @xml.value('/Event[1]/Username[1]', 'varchar(30)'); 

Answer 3

use .value () instead of .query ()