SQL Server 2008: SQLXML Query function .query('//mypath/text()') doesn't XML decode

Question

In Microsoft SQL Server 2008, when executing .query('{xpath}/text()') on an XML stream column value, if the value of the XPath selected node contains "&", the return value is "&amp" instead of "&".

Is this a bug, or am I doing something wrong? Or rather, how do I get the unencoded text, i.e. equivalent of .innerText (per W3C XML DOM), with SQLXML XPath if text() in the XPath query isn't supposed to do that?

Answer 1

No bug, SQL XML decodes XML escape sequences correctly:

declare @x xml;
set @x = '<node>&amp;some</node>';
select @x.value(N'(node)[1]', N'varchar(max)');

What most likely is your problem is a misunderstanding on your part of the XML query method: it returns an XML fragment, and as such all content will be... escaped, since it's XML:

declare @x xml;
set @x = '<node>&amp;some</node>';
select @x.query(N'/node/text()');