Extra Characters while using XML PATH

Solution 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.