In Below script in Place of & it is displaying & how to avoid amp;

  • DBA.k

    Ten Centuries

    Points: 1085

    In Below script in Place of & it is displaying & how to avoid amp;

    I don't want replace . In STUFF and XMLPATH is there any chance to avoid this ?

    DECLARE @TEMP TABLE(ID INT, [VALUE] NVARCHAR(30))

    INSERT INTO @TEMP VALUES(1 , 'MAZ')

    INSERT INTO @TEMP VALUES(1 , 'HON & DON')

    INSERT INTO @TEMP VALUES(1 , 'FOR')

    INSERT INTO @TEMP VALUES(2 , 'JEEP')

    INSERT INTO @TEMP VALUES(2 , 'CHE')

    --------------------WITH STUFF FUNCTION

    SELECT [id],

    Stuff((SELECT ',' + [VALUE]

    FROM @TEMP

    WHERE [id] = a.[id]

    FOR xml path('')), 1, 1, '') [VALUE]

    FROM @TEMP a

    GROUP BY [id]

  • Jonathan AC Roberts

    SSCoach

    Points: 17293

    SELECT [id],
    Stuff((SELECT ',' + [VALUE]
    FROM @TEMP
    WHERE [id] = a.[id]
    FOR xml path(''), root('MyString'), type).value('/MyString[1]','varchar(max)'),1, 1, '') [VALUE]
    FROM @TEMP a
    GROUP BY [id]

    http://blogs.lobsterpot.com.au/2010/04/15/handling-special-characters-with-for-xml-path/

  • DBA.k

    Ten Centuries

    Points: 1085

    Thank you ... so much..

  • John Mitchell-245523

    SSC Guru

    Points: 148761

    Have a read through this.  It covers the issue you're having.

    John

    Edit: oops - didn't see Jonathan's reply

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply