I'm trying to use CHAR(13) to force a new line, but the problem is, I'm doing this within a FOR XML Select statement:
SELECT
STUFF((SELECT CHAR(13) + Comment
FROM
myTable
FOR XML PATH ('')) , 1, 1, '')
The problem with this, is that I don't get an actual new line. Instead, I get:
#x0D;
So the data literally looks like this:
#x0D;First Line of Data#x0D;Second Line of Data#x0D;Etc
So I tried to just replace #x0D; with CHAR(13) outside of the FOR XML:
REPLACE(SELECT
STUFF((SELECT CHAR(13) + Comment
FROM
myTable
FOR XML PATH ('')) , 1, 1, '')), '#x0D;', CHAR(13))
This gets me close. It DOES add in the line breaks, but it also includes an & at the end of each line, and the start of each line after the first:
First Line of Data&
&Second Line of Data&
&Etc
Thanks everyone for your help.
The ultimate goal here was to present the data in Excel as part of a report. I'm sure there is a more elegant way to do this, but I at least got the results I wanted by doing this:
REPLACE (
REPLACE(
REPLACE(
(SELECT Comment FROM CallNotes WHERE ForeignId = a.ForeignId FOR XML PATH (''))
, '<Comment>', '')
, '</Comment>', CHAR(13) + CHAR(10))
, '
', '') AS Comments
The select statement all by itself returns XML as we would expect:
<comment>This is a comment</comment><comment>This is another comment</comment>
The inner most REPLACE just gets rid of the opening tag:
<comment>
The middle REPLACE removes the closing tag:
</comment>
and replaces it with CHAR(13) + CHAR(10). And the outer most REPLACE gets rid of this:

(I still don't understand where that's coming from.)
So, when the results are sent to Excel, it looks like this inside the cell:
This is a comment.
This is another comment.
Which is exactly what I want. Again, I'm sure there is a better solution. But this at least is working for now.