Post reply

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

  • July 10, 2019 at 1:43 pm

    #3661580

    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]

  • July 10, 2019 at 1:54 pm

    #3661584 

    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/

  • July 10, 2019 at 2:22 pm

    #3661593

    Thank you ... so much..

  • July 10, 2019 at 2:47 pm

    #3661606

    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 3 (of 3 total)

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