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?

score 4 · Accepted Answer · answered Mar 04 '10 at 21:55

4

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()');

answered Mar 04 '10 at 21:55

Remus Rusanu

288,378
40
442
569

So then how do you get the decoded *text*, i.e. the .InnerText as equivalent in XML DOM? Or is that possible in SQLXML? – Jon Davis Mar 04 '10 at 22:52
My first code snippet, using value('(node)[1]') shows just that, it returns the XML element node value (ie. the `text()`). – Remus Rusanu Mar 04 '10 at 22:59
How in XPath? My desired node is not @ root. – Jon Davis Mar 05 '10 at 15:40
.. I figured it out .. CAST({column} as XML).query('{xpath query}').value(N'({node})[1]', N'varchar(max)') – Jon Davis Mar 05 '10 at 15:45
2

or rather .value(N'({xpath query})[1]', N'varchar(max)') – Jon Davis Mar 05 '10 at 16:07

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

1 Answers1