Casey Crookston Casey Crookston - 2 months ago 37
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:

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

Answer

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))
, '&#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:

<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:

&#x0D;  

(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.

Comments