FOR XML PATH: Decode Escaped Text Through TYPE

I keep FOR XML PATH escaping separate from the original text value. Serialization represents reserved XML characters safely. TYPE returns XML that can be read through its scalar method instead of manually replacing entity spellings.

Start with text containing reserved characters

The input is A&B<C, which includes an ampersand and a less-than character. Both expressions serialize the same supplied text. The output without TYPE is a string representation containing XML entity spellings.

The expected SerializedText is A&amp;B&lt;C. Those extra characters belong to the serialization, not to a newly changed business label. I’d keep the original text contract in view whenever an XML-producing query is used as an intermediate step.

WITH Encoded AS
(
 SELECT (SELECT N'A&B<C' AS [text()] FOR XML PATH('')) AS SerializedText,
        (SELECT N'A&B<C' AS [text()] FOR XML PATH(''),TYPE) AS XmlValue
)
SELECT CAST(SerializedText AS nvarchar(200)) AS SerializedText,
       CAST(XmlValue.value('(text())[1]','nvarchar(max)')
            AS nvarchar(200)) AS DecodedText
FROM Encoded;

Request an XML result with TYPE

The TYPE directive returns an XML instance. Without TYPE, FOR XML returns string data. That difference lets the second expression be processed with the XML value method.

The query reads its text node as nvarchar(max), then applies a small outer display cast. The expected DecodedText is the original A&B<C. I keep the method call visible. The recovery follows XML processing rather than an unexplained string rewrite.

Avoid a chain of manual replacements

Replacing entity spellings one by one can confuse serialized representation with source text that already contains similar characters. The order of those replacements can also matter. A short friendly example doesn’t establish that such a chain is safe.

I’d use the typed XML result when that is the intended intermediate representation. Its scalar extraction follows the XML value rather than guessing from a displayed string. That keeps the operation connected to the format that produced the escaping.

Keep the singleton extraction explicit

The value call requests the first text node through a singleton path. The supplied XML result contains this one text value, so the extraction matches the demonstration’s contract. Its requested SQL type is explicit.

I wouldn’t generalize that first-node expression to arbitrary XML fragments with several independent nodes. The consumer’s intended value needs a clear path. Selecting one node can discard other content, so a more complex source requires its own extraction review.

Review capacity and input validity

Both displayed columns use nvarchar(200), comfortably larger than these short values. A production result with longer text needs its own destination capacity. The TYPE directive doesn’t make a later narrow cast unlimited.

XML also has character-validity rules beyond ordinary string storage. I’d test representative input if text can contain control characters. This example uses valid characters with reserved markup meaning. It isolates escaping without implying that every string is valid XML.

Keep this separate from aggregation policy

The query supplies one text value and creates no table or stored object. It demonstrates serialization and scalar recovery, not a recommendation to assemble every list through XML. Ordering and NULL handling for a real aggregation are separate decisions.

I’d compare both complete expected strings when validating the example. A row count wouldn’t reveal a lost ampersand or a still-escaped less-than character. The useful result is the connection between the serialized representation and the text value that the consumer expects.

Let the XML method do the decoding, and the text comes back whole.

An XML entity is not the original text, it is serialization that typed extraction decodes.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.