I have the following query inside a larger Select statement in SQL Server:
CONVERT(NVARCHAR(2000),stuff((SELECT '; ' + IsNull(D2.SelectedComments,'')
FROM #StudentDetails D2
WHERE D2.STUD_PK = A.STUD_PK AND D2.CourseNo = A.CourseNo
AND D2.Section = A.Section
FOR XML PATH('')),1,2,'')) AS SelectedComments,
This column is generating some strange symbols after certain entries such as This approach is satisfactory .
. I don't understand where the .
is coming from. I tried doing SELECT SelectedComments FROM #StudentDetails
right before this and I don't see the .
. Can anyone tell where it could be coming from?
If you modify your use of For Xml Path, it will do the unescaping for you and you won't need to resort to using the Replace function:
, Stuff(
(
Select '; ' + IsNull( D2.SelectedComments, '' )
From #StudentDetails As D2
Where D2.Stud_PK = A.Stud_PK
And D2.CourseNo = A.CourseNo
And D2.Section = A.Section
For Xml Path(''), type
).value('.', 'nvarchar(max)')
, 1, 2, '') As SelectedComments
The 
 is a carriage return. You can either clean up the data before inserting it, remove it, or, if you want to keep the formatting, add TYPE to the end of your SELECT:
SELECT * FROM MyData FOR XML PATH(''), TYPE
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With