reporting-servicesibm-midrangereport-builder2.0

How do I handle ampersand encoding in Report Builder?

I have a Select statement that combines multiple rows into 1 result row using FOR XML PATH in Report Builder 2.0. I am pulling the data from an IBM i520 server. When a row has an ampersand in it I get a & How do I clean this up in my select statement ?

SELECT CAST(Q5TXLN + ' ' AS VARCHAR(MAX))FROM 520SERVER WHERE Q5$CPN = @pdCustNo AND Q5TP = 'PDS' FOR XML PATH ('')
Solution 
  • Select
REPLACE(
cast((SELECT CAST(replace(Q5TXLN,'&','#') + ' ' AS VARCHAR(MAX))FROM [520SERVER] WHERE Q5$CPN = @pdCustNo AND Q5TP = 'PDS' FOR XML PATH (''),type) as varchar(max))
,'#','&')