Solución:
Gracias a todos por su ayuda.
El objetivo final aquí era presentar los datos en Excel como parte de un informe. Estoy seguro de que hay una forma más elegante de hacer esto, pero al menos obtuve los resultados que quería al hacer esto:
REPLACE (
REPLACE(
REPLACE(
(SELECT Comment FROM CallNotes WHERE ForeignId = a.ForeignId FOR XML PATH (''))
, '<Comment>', '')
, '</Comment>', CHAR(13) + CHAR(10))
, '
', '') AS Comments
La declaración de selección por sí sola devuelve XML como cabría esperar:
<comment>This is a comment</comment><comment>This is another comment</comment>
El REPLACE más interno simplemente elimina la etiqueta de apertura:
<comment>
El REPLACE del medio elimina la etiqueta de cierre:
</comment>
y lo reemplaza con CHAR (13) + CHAR (10). Y el REPLACE más externo se deshace de esto:

(Todavía no entiendo de dónde viene eso).
Entonces, cuando los resultados se envían a Excel, se ve así dentro de la celda:
This is a comment.
This is another comment.
Que es exactamente lo que quiero. Nuevamente, estoy seguro de que hay una solución mejor. Pero esto al menos está funcionando por ahora.
Tu enfoque no es XML real:
Intente esto con “salida a texto”:
DECLARE @tbl TABLE(TestText VARCHAR(100));
INSERT INTO @tbl VALUES('line 1'),('line 2'),('line 3');
SELECT STUFF
(
(
SELECT CHAR(10) + tbl.TestText
FROM @tbl AS tbl
FOR XML PATH('')
),1,1,''
)
Con CHAR(13)
#x0D;line 1
line 2
line 3
Mira que tu STUFF acaba de quitar el ampersand?
Con CHAR(10)
line 1
line 2
line 3
Pero lo que realmente necesitas es:
SELECT STUFF
(
(
SELECT CHAR(10) + tbl.TestText --you might use 13 and 10 here
FROM @tbl AS tbl
FOR XML PATH(''),TYPE
).value('.','nvarchar(max)'),1,1,''
)
los ,TYPE volverá XML real y con .value() lee esto correctamente.
Algunos antecedentes
Tienes una idea errónea de “Entonces, los datos literalmente se ven así”
No es asi “se parece a esto”, se escapa para ajustarse a las reglas dentro de XML. Y se volverá a codificar implícitamente, cuando lo lea correctamente.
Y tiene una idea errónea de los saltos de línea:
En (casi) tiempos antiguos necesitabas un CR = Carriage Return, 13 or x0D para mover hacia atrás el trineo de impresión y, además, necesitaba un LF = Line Feed, 10 or x0A para girar la platina para mover el papel. De ahí la ampliamente utilizada necesidad de tener un salto de línea codificado con dos caracteres (13/10 o 0D / 0A).
Hoy en día, el ASCII 10 (0A) a menudo se ve solo …
Pero volvamos a su problema real: ¿Por qué se preocupa por la Mira de sus datos? Dentro de XML, algunas cadenas pueden verse feas, pero … si lees esto correctamente – la decodificación de vuelta a la apariencia original se hace implícitamente …
Tu residuos no son más que parte de la codificación, ya que comienza con un ampersand y termina con un punto y coma: ≶ o 
. Su intento de reemplazar esto es solo un carácter para abreviar. Pero de todos modos: ¡No deberías hacer esto!
Sólo inténtalo:
SELECT CAST('<x>Hello</x>' AS XML).value('/x[1]','nvarchar(max)')
Creo que esto está más limpio. Básicamente, comience con avances de línea (o algún otro carácter especial) y luego reemplácelos con retornos de carro más avances de línea si lo desea.
Select REPLACE(STUFF((SELECT CHAR(10) + Comment
FROM myTable FOR XML PATH ('')) , 1, 1, ''),
CHAR(10), CHAR(13)+CHAR(10))