I did not go though all the replies but am throwing this out there.

I had to do the same thing a few years ago and here is how I did it (was processing over a million records straight from a table to an xml file using ssis)...

DECLARE @XmlOutput xml

SET @XmlOutput =

(

select RecordType "Participant/recordtype",

ProfileID "Participant/accountnumber"

from dbo.Table

where 1=1

and Report_Year = @Report_Year

and Report_Month = @Report_Month

for xml path(''),

root('PutRootHere')

)

SELECT @XmlOutput