In SQL server you might have a parent table which have set of rows which have related data in a child table which have 1 to many relationship in between parent and child rows so in your queries you might need to generate comma separated list of child data for each row that of parent table. You can do this with a loop or with cursor but there is much efficient way to do it with FOR XML PATH in SQL server.
consider following query where you need to get comma separated list of accommodations in each accommodation group.
SELECT ag.AccGrpID , ( SELECT NAME + ',' FROM dbo.Accommodation ac WHERE ac.AccGrpID = ag.accgrpid FOR XML PATH('') ) AS CommaSeperatedAccommodations FROM dbo.AccommodationGroup ag
But what is happening here? when you run a FOR XML PATH query against set of data in SSMS it will Return scalar value which contains XML (of course this is XML data type representation). Try converting it in to NVARCHAR(4000) or NVARCHAR(MAX) you get result set without XML element names. This conversion is implicitly happen when you run above query because when you do a + ‘,’ in code XML data get converted to NVARCHAR resulting comma separated list. This list will return a comma separated list with additional trailing comma but you can easily remove it with string manipulation function in TSQL.