TSQL: For XML Path

To retrieve results of a SQL query as XML, specify FOR XML clause in the query.
FOR XML has PATH as one of its modes. It provides a simpler way to mix elements and attributes.

In PATH mode, column names or column aliases are treated as XPATH expressions, which indicate how the values are being mapped to XML.

To better illustrate the use of FOR XML PATH, I have this table #tmp:

create table #tmp
 (num int)

 insert into #tmp
 values(1),(2),(3)

Script	Output
SELECT num from #tmp FOR XML PATH	<row><num>1</num></row><row><num>2</num></row><row><num>3</num></row>
SELECT cast(num as varchar) from #tmp FOR XML PATH	<row>1</row><row>2</row><row>3</row>
SELECT num from #tmp FOR XML PATH ('')	<num>1</num><num>2</num><num>3</num>
SELECT cast(num as varchar) from #tmp FOR XML PATH ('')	123
SELECT cast(num as varchar) from #tmp FOR XML PATH (''), type	123
SELECT cast(num as varchar) from #tmp FOR XML PATH (''), type	,1,2,3
select (SELECT ',' + cast(num as varchar) from #tmp FOR XML PATH (''), type).value('.', 'varchar(max)')	,1,2,3
select stuff((SELECT ',' + cast(num as varchar) from #tmp FOR XML PATH (''), type).value('.', 'varchar(max)'), 1, 1, '')	1,2,3

I just used my samples on one of my query requirements. More examples in Books Online.