Here is a generic example.

You could adjust it for your needs.

declare @x xml;

--== Grab some sample xml ==--

set @x = (select * from sysprocesses for xml auto,elements,type,binary base64);

--== Get a list of headers for the CSV ==--

-- The assumption has been made that we want each top level node as one row in the CSV

-- If this is not correct, modify the FROM clause accordingly

select

stuff(

convert(varchar(max),

nd.query('

for $n in *

return concat(",""",local-name($n),"""")

')

),1,1,''

) as data

from @x.nodes('(*)[1]') as x(nd)

--== The UNION ALL is optional, but it makes sense to me to have the headers with the data ==--

-- this does that

union all

--== Now list the data as CSV for each top level node ==--

-- same assumption as above for the header, adjust as required

select

stuff(

convert(varchar(max),

nd.query('

for $n in *

return

concat(",""",string($n),"""")

')

),1,1,''

) as data

from @x.nodes('*') as x(nd);

Which produces output like this:

"spid" ,"kpid" ,"blocked" ,"waittype" ,"waittime" ,"lastwaittype" ,"waitresource" ,"dbid" ,"uid" ,"cpu" ,"physical_io" ,"memusage" ,"login_time" ,"last_batch" ,"ecid" ,"open_tran" ,"status" ,"sid" ,"hostname" ,"program_name" ,"hostprocess" ,"cmd" ,"nt_domain" ,"nt_username" ,"net_address" ,"net_library" ,"loginame" ,"context_info" ,"sql_handle" ,"stmt_start" ,"stmt_end" ,"request_id"

"1" ,"2412" ,"0" ,"AIM=" ,"49" ,"LOGMGR_QUEUE " ," " ,"0" ,"1" ,"78" ,"0" ,"0" ,"2013-04-25T09:30:24.007" ,"2013-04-25T09:30:24.007" ,"0" ,"0" ,"background " ,"AQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=" ," " ," " ," " ,"LOG WRITER " ," " ," " ," " ," " ,"sa " ,"AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=" ,"AAAAAAAAAAAAAAAAAAAAAAAAAAA=" ,"0" ,"0" ,"0"

"2" ,"3120" ,"0" ,"AKE=" ,"141055089" ,"KSOURCE_WAKEUP " ," " ,"1" ,"1" ,"0" ,"0" ,"5" ,"2013-04-25T09:30:24.027" ,"2013-04-25T09:30:24.027" ,"0" ,"0" ,"background " ,"AQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=" ," " ," " ," " ,"SIGNAL HANDLER " ," " ," " ," " ," " ,"sa " ,"AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=" ,"AAAAAAAAAAAAAAAAAAAAAAAAAAA=" ,"0" ,"0" ,"0"

"3" ,"3124" ,"0" ,"Axw=" ,"25" ,"DIRTY_PAGE_POLL " ," " ,"0" ,"1" ,"46" ,"0" ,"0" ,"2013-04-25T09:30:24.030" ,"2013-04-25T09:30:24.030" ,"0" ,"0" ,"background " ,"AQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=" ," " ," " ," " ,"RECOVERY WRITER " ," " ," " ," " ," " ,"sa " ,"AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=" ,"AAAAAAAAAAAAAAAAAAAAAAAAAAA=" ,"0" ,"0" ,"0"

"4" ,"2500" ,"0" ,"AIQ=" ,"3079" ,"REQUEST_FOR_DEADLOCK_SEARCH " ," " ,"0" ,"1" ,"15" ,"0" ,"0" ,"2013-04-25T09:30:24.030" ,"2013-04-25T09:30:24.030" ,"0" ,"0" ,"background " ,"AQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=" ," " ," " ," " ,"LOCK MONITOR " ," " ," " ," " ," " ,"sa " ,"AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=" ,"AAAAAAAAAAAAAAAAAAAAAAAAAAA=" ,"0" ,"0" ,"0"

This is not production code, just something I banged together, there are known issues, such as it not handling double quotes or commas in the data, but it's kind of interesting to see how easy it can be.