Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Escaping ampersand in FOR XML Expand / Collapse
Author
Message
Posted Thursday, December 11, 2008 2:53 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:44 PM
Points: 69, Visits: 205
Does anybody know a way to prevent SQL 2005 from entitizing the ampersand in a FOR XML statement?

Ex:
SELECT 'joe&bob@mail.com' + ';' FOR XML PATH('')
returns 'joe&bob@mail.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?
Post #618256
Posted Thursday, December 11, 2008 3:04 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:44 PM
Points: 69, Visits: 205
It looks like IE and FF display this differently.
Basically I want the result of FOR XML not to have any 'amp;' in the result when an ampersand is present.
Post #618262
Posted Friday, December 12, 2008 9:05 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:26 AM
Points: 775, Visits: 1,203
I don't know anyway around how to escape/preserve the special characters in XML, since they indeed are SPECIAL
like &, , etc...
http://www.xml.com/pub/a/2001/01/31/qanda.html


Someone asked this question on StackOverflow (Google)
http://stackoverflow.com/questions/270948/how-to-preserve-an-ampersand-while-using-for-xml-path-on-sql-2005



SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #618658
Posted Tuesday, December 16, 2008 9:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:44 PM
Points: 69, Visits: 205
My resolution to this issue was to use %26 in the database for an ampersand, then replace it on the client end with '&'
Post #620521
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse