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;

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)