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