Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL 'stuff' and 'FOR XML PATH' generating strange symbols

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 .&#x0D. I don't understand where the .&#x0D is coming from. I tried doing SELECT SelectedComments FROM #StudentDetails right before this and I don't see the .&#x0D. Can anyone tell where it could be coming from?

like image 776
Art F Avatar asked May 14 '13 15:05

Art F


2 Answers

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
like image 143
Thomas Avatar answered Sep 28 '22 02:09

Thomas


The &#x0D 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
like image 37
M.Ob Avatar answered Sep 28 '22 03:09

M.Ob