Generate CRLF in TSQL to be accepted by SSRS

Question

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?

Answer 1

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;