Casey Crookston Casey Crookston - 1 month ago 12x
SQL Question

Using CHAR(13) in a FOR XML SELECT

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:

STUFF((SELECT CHAR(13) + Comment
FOR XML PATH ('')) , 1, 1, '')

The problem with this, is that I don't get an actual new line. Instead, I get:


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:

STUFF((SELECT CHAR(13) + Comment
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&


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:

            (SELECT Comment FROM CallNotes WHERE ForeignId = a.ForeignId FOR XML PATH (''))
        , '<Comment>', '')  
    , '</Comment>', CHAR(13) + CHAR(10))
, '&#x0D;', '') 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:


The middle REPLACE removes the closing tag:


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.