SQL“东西"和“FOR XML PATH"生成奇怪的符号

用户注册

投稿取消

文章分类:

还能输入300字

上传中....

问题描述

我在 SQL Server 中较大的 Select 语句中有以下查询:

 CONVERT(NVARCHAR(2000),stuff((SELECT '; ' + IsNull(D2.SelectedComments,'')来自#StudentDetails D2WHERE D2.STUD_PK = A.STUD_PK AND D2.CourseNo = A.CourseNoAND D2.Section = A.SectionFOR XML PATH('')),1,2,'')) AS SelectedComments,

此列在某些条目后生成一些奇怪的符号，例如这种方法令人满意.&#x0D.我不明白 .&#x0D 来自哪里.在此之前，我尝试执行 SELECT SelectedComments FROM #StudentDetails，但我没有看到 .&#x0D.谁能告诉它可能来自哪里?

解决方案

如果你修改你对 For Xml Path 的使用，它会为你做转义，你不需要求助于使用 Replace 函数:

, 东西((选择 ';' + IsNull( D2.SelectedComments, '' )来自#StudentDetails 作为 D2其中 D2.Stud_PK = A.Stud_PK和 D2.CourseNo = A.CourseNoD2.Section = A.Section对于 Xml Path('')，输入).value('.', 'nvarchar(max)'), 1, 2, '') 作为 SelectedComments

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?

解决方案

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