Does anybody know a way to prevent SQL 2005 from entitizing the ampersand in a FOR XML statement?
SELECT 'firstname.lastname@example.org' + ';' FOR XML PATH('')
returns 'joe&amp;email@example.com;' and I want to just get the '&' no 'amp;'
I'm using this with a contact table of email addresses. Based on different criteria a UNION of SELECTed email addresses becomes a semicolon-delimited string, nicely concatenated by FOR XML at the end of all the UNIONs. This is used by a client application that splits the string into email address, delimiting on the semicolon. Obviously the additional semicolon in the middle of an address causes problems.
Is there an 'inline' method for telling SQL to not entitize the ampersand?