Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Format XML output using FOR XML

Author  Topic  spegase Starting Member 1 Post Posted - 2009-04-01 : 08:37:23 Hello I have some problems for format the data output from sql server using FOR XML. Here is what I would like to do: - 1 table - in this table 2 columns, ID (int) + Data (XML) the records contains an incrementation of the ID, and block of xml data. e.g.: ID XML 1 <hello><myname>alex</myname><myplanet>earth</myplanet></hello> 2 <hello><myname>frank</myname><myplanet>mars</myplanet></hello> 3 <hello><myname>stive</myname><myplanet>venus</myplanet></hello> I want to call an SP and get a resultat like this <infos> <info count="1"> <hello><myname>alex</myname><myplanet>earth</myplanet></hello> </info> <info count="2"> <hello><myname>frank</myname><myplanet>mars</myplanet></hello> </info> <info count="3"> <hello><myname>stive</myname><myplanet>venus</myplanet></hello> </info> </infos> I tried to use FOR XML PATH('infos') but this include the extra tag "XML". I'm not sure how to do this. Thank you for the help :-)