Use XML to concatenate multiple records into a single field in MSSQL

concat-with-xml.sql

select
  s.name,
  stuff ( 
    ( SELECT ',' + c.name AS [text()] FROM cities c where c.stateid=s.id 
      FOR XML PATH('') ),
    1, 1, '') cities
from states s;

select
  s.name,
  ( SELECT '<city>' + c.name + '</city>' AS [text()] FROM cities c where c.stateid=s.id 
    FOR XML PATH('') 
  ) cities
from states s;