FOR XML to rows

Transact-SQL
1

Hi All,

just wondering if this is do-able?

Im looking to run sql query and create an xml output but instead of grouping it all in the one "column" I'm looking to return it into multi rows.

so this sql: SELECT top 5 user_id,user_name FROM aaguser FOR XML PATH ('Users'),ROOT ('Employee')

brings back this:

I would like to return the users section of the xml as rows rather than one group like this. so each row would contain the data (users)............(/users)

Any info /help would be great.

Cheers
Patrick

2

I didn't quite understand what you are asking. Can you type out and post a sample of the output you are looking for? Perhaps this?

select 
	b.xmlcol 
from
	aaguser a
	cross apply
	(	 select user_id, user_name from aaguser b
		 where a.userid = b.userid -- or whatever your pk is
		 for xml path ('users'), type
	)b(xmlcol)