How to preserve an ampersand (&) while using FOR XML PATH on SQL 2005

There are situations where a person may not want well formed XML - the one I (and perhaps the original poster) encountered was using the For XML Path technique to return a single field list of 'child' items via a recursive query. More information on this technique is here (specifically in the 'The blackbox XML methods' section):Concatenating Row Values in Transact-SQL

For my situation, seeing 'H&E' (a pathology stain) transformed into 'well formed XML' was a real disappointment. Fortunately, I found a solution... the following page helped me solve this issue relatively easily and without having re-architect my recursive query or add additional parsing at the presentation level (for this as well for as other/future situations where my child-rows data fields contain reserved XML characters): Handling Special Characters with FOR XML PATH

EDIT: code below from the referenced blog post.

select  stuff(     (select ', <' + name + '>'     from sys.databases     where database_id > 4     order by name     for xml path(''), root('MyString'), type     ).value('/MyString[1]','varchar(max)')   , 1, 2, '') as namelist;

What SQL Server generates is correct. What you expect to see is not well-formed XML. The reason is that & character signifies the start of an entity reference, such as &. See the XML specification for more information.

When your XML parser parses this string out of XML, it will understand the & entity references and return the text back in the form you want. So the internal format in the XML file should not cause a problem to you unless you're using a buggy XML parser, or trying to parse it manually (in which case your current parser code is effectively buggy at the moment with respect to the XML specification).

Try this....

select   stuff(      (select ', <' + name + '>'      from sys.databases      where database_id > 4      order by name      for xml path(''), root('MyString'), type      ).value('/MyString[1]','varchar(max)')    , 1, 2, '') as namelist;