In TSQL I concatenate different rows to one cell with: FOR xml path('')
I place the next characters after every line to produce a linefeed.
+ char(13) + char(10)
SSRS does generate a linefeed but also prints these CRLF characters.
Every line ends with: 
Is there a solution?
The XML you produce with a simple FOR XML PATH('')
(in order to do something like STRING_AGG()
) will translate any forbidden character (such as <, > and &
and many more) to the correspondig entity. An entity is &abc;
, while abc
stands for a character (like <
) or you see some kind of hexadecimal or unicode code point. This is done while creating the XML implicity. Reading something out of this XML will do the invers process - again implicitly.
But: Your code does not return the result as the native XML type.
Try this (best switch the output to "text"):
SELECT TOP 5 o.[name] + CHAR(13) + CHAR(10)
FROM sys.objects o
FOR XML PATH('')
returns
sysrscols
sysrowsets
sysclones
sysallocunits
sysfiles1
But if you handle the XML as a typed result, you can use .value()
:
SELECT
(
SELECT TOP 5 o.[name] + CHAR(13) + CHAR(10)
FROM sys.objects o
FOR XML PATH(''),TYPE
).value('.','nvarchar(max)');
returns
sysrscols
sysrowsets
sysclones
sysallocunits
sysfiles1
You did not show any code, but I assume that you've got something like
SELECT SomeColumn
,(SELECT blah + CHAR(13) + CHAR(10)
FROM SomeInnerTable
WHERE SomeCondition
FOR XML PATH('')) AS SomeAlias
FROM SomeOuterTable;
Change this to
SELECT SomeColumn
,(SELECT blah + CHAR(13) + CHAR(10)
FROM SomeInnerTable
WHERE SomeCondition
FOR XML PATH(''),TYPE).value('.','nvarchar(max)') AS SomeAlias
FROM SomeOuterTable;