Concatenate Rows with for xml logic understanding

• Megha P

  SSCommitted

  Points: 1976

  More actions

  February 24, 2015 at 1:59 am

  #314955

  Hi,

  i am not able to understand why just appending blank string in below code removes '<item>' from xml result.Is it converting to varchar datatype ?

  this question is just for my understanding

  CREATE TABLE #tbl

  (id INT IDENTITY(1,1),

  item varchar(100))

  INSERT #tbl

  SELECT 'This'

  UNION ALL

  SELECT'is'

  UNION ALL

  SELECT 'test'

  UNION ALL

  SELECT 'string'

  SELECT item FROM #tbl FOR XML PATH('')

  SELECT ' '+ item FROM #tbl FOR XML PATH('')

  Thanks..

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  February 24, 2015 at 2:11 am

  #1779507

  You need to provide a name for the column

  SELECT ' '+ item AS item FROM #tbl FOR XML PATH('')

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Megha P

  SSCommitted

  Points: 1976

  More actions

  February 24, 2015 at 2:28 am

  #1779510

  In normal result set column value won't affected with column name provided, is this specific to xml?

  I just want to understand logic behind it ,is it converting datatype ?

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  February 24, 2015 at 2:31 am

  #1779511

  This is specific to XML, it's using the column name as part of the results. There's no data type conversion going on.

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Megha P

  SSCommitted

  Points: 1976

  More actions

  February 24, 2015 at 2:36 am

  #1779513

  ok..Thank you Mark