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

• DBA.k

  Ten Centuries

  Points: 1113

  More actions

  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]

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  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/

• DBA.k

  Ten Centuries

  Points: 1113

  More actions

  July 10, 2019 at 2:22 pm

  #3661593

  Thank you ... so much..

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  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

  • This reply was modified 5 years, 10 months ago by  John Mitchell-245523.