I have the following code to derive a column in a view:
SELECT UPPER(PED_AGENCY.AGENCY_NAME) + ',' AS [text()] FROM PED_AGENCY INNER JOIN PED_AGENTS_SEGMENTS_AGENCY_RELATIONSHIP AGREL ON PED_AGENCY.AGENCY_ID=AGREL.AGENCYID WHERE (AGREL.AGENTID=GS.AGENTID) AND (AGREL.STARTDATE >= GS.STARTDATE) AND (AGREL.ENDDATE IS NULL OR AGREL.ENDDATE<= GS.ENDDATE) FOR XML PATH('') ) AS 'AGENCY',
The result is what I need (splitting out multiple records into one delimited set of values in one column), however, in any case where the original value(s) had an ampersand, such as the value: D&S Preston Partnership, the following occurs: In cases where there actually WERE multiple values (such as D&S Preston Partnership and something else), it gets messed up and shows up as
D&S PRESTON PARTNERSHIP but in cases where that was the only value, it shows up OK.
Something to do with my casting as text and/or my concatenation? Any help to correct this much appreciated.