Why does SQL display an & as &? [duplicate]

Question

Possible Duplicate:
help with FOR XML PATH('') escaping “special” characters

I need some assistance, my query is below:

STUFF(
    (
      SELECT ',' + CountDesc
      FROM Count INNER JOIN ProjectCount ON Count.Id =  ProjectCount.CountId 
      WHERE ProjectCount.ProjectId = Project.Id ORDER BY Count.CountDesc
      FOR XML PATH('')
    ), 1, 1, '') as  [Country]

What happens is when i run this query and the Count table has an & in one of its fields, it displays the & as &.

Is there anyway to not let this happen?

Thanks in advance.

Answer 1

It is happening because the strings being combined in the XML statement are using XML specific characters. In addition to &, the also affects < and >, and probably other characters.

I usually fix this be doing a replace after the call:

select @str = replace(@str, '&', '&')

And nesting the replaces for additional characters.

Answer 2

Per Section 2.4 of the XML spec, & must be escaped except for in a few special cases (e.g. within a comment or CDATA section). If the & wasn't displayed as &, the XML would be invalid.