Extra Characters while using XML PATH

Question

I have a table called Map_Data and the data looks like:

 ID    SoCol                                                              Descol  
 125   case Per_rating when 5 then 'Good' when 4 then 'Ok' else null end  D_Code

And I wrote a query on this particular row and the query is:

SELECT  Params = ( SELECT   DesCol + ' = ''' + SoCol + ''''
                   FROM     dbo.Map_Data  t1
                   WHERE   ID = 125
                   FOR
                   XML PATH('')
                 )

and I get the output as :

D_Code = 'case per_rating
 when 5 then 'Good'
 when 4
 then 'Ok'
 end'

Can anyone tell me why i am getting '
' it and how can i correct it?

Answer 1

This slight change will make the ugly entities go away, but they won't eliminate carriage returns (look at the results in Results to Text, not Results to Grid, to see them):

SELECT  Params = ( SELECT   DesCol + ' = ''' + SoCol + ''''
                   FROM     dbo.Map_Data  t1
                   WHERE   ID = 125
                   FOR
                   XML PATH(''), TYPE
                 ).value(N'./text()[1]', N'nvarchar(max)');

If you want to get rid of the CR/LF too you can say:

SELECT  Params = ( SELECT   REPLACE(REPLACE(DesCol + ' = ''' + SoCol + '''', 
     CHAR(13), ''), CHAR(10), '')
                   FROM     dbo.Map_Data  t1
                   WHERE   ID = 125
                   FOR
                   XML PATH(''), TYPE
                 ).value(N'./text()[1]', N'nvarchar(max)');

Also I'm not sure how you're going to use the output but if you're going to evaluate it later with dynamic SQL you're going to need to replace the embedded single quotes (') with two single quotes (''). Otherwise it will blow up because they're also string delimiters.