Using CHAR(13) in a FOR XML SELECT
<p>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:</p>

<pre><code>SELECT 
    STUFF((SELECT CHAR(13) + Comment 
FROM 
   myTable 
FOR XML PATH ('')) , 1, 1, '')
</code></pre>

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

<pre><code>#x0D;
</code></pre>

<p>So the data literally looks like this:</p>

<pre><code>#x0D;First Line of Data#x0D;Second Line of Data#x0D;Etc
</code></pre>

<p>So I tried to just replace #x0D; with CHAR(13) outside of the FOR XML:</p>

<pre><code>REPLACE(SELECT 
    STUFF((SELECT CHAR(13) + Comment 
FROM 
   myTable 
FOR XML PATH ('')) , 1, 1, '')), '#x0D;', CHAR(13))
</code></pre>

<p>This gets me close.  It DOES add in the line breaks, but it also includes an &amp; at the end of each line, and the start of each line after the first:</p>

<pre><code>First Line of Data&amp;
&amp;Second Line of Data&amp;
&amp;Etc
</code></pre>