I would tend to agree with Mike Dougherty-384281. This approach seems like a bad idea for a number of reasons.
Why not use a "FOR XML" query and transform the XML on a web page?
I couldn't tell if your question is rhetorical, a suggestion, or an inquiry.
I would avoid using FOR XML on the general principle that it's still a large volume of string manipulation even if in the guise of a native feature. Mostly we're still using SQL Server 2000 instances and what I read of its newfangled XML capability made me think it should be avoided. Perhaps '2008 has evolved sufficiently that it's a viable solution.
If your proposed XML transform on the web page is using DOM methods on the XML document, the performance win you might be getting from requesting XML directly from the database (at the cost of exposing the server directly to clients?) you then pay in DOM method performance. A web service in front of the SQL Server minimizes server exposure, gives a web-side cache opportunity, and can probably respond to client-side/browser xmlhttp requests with JSON.
The author's original post is an example of a possible solution. As the opening move in the discussion that follows, I want to thank Raja Mohamed for taking the time to write it up. I felt it was a worthwhile contribution to this community to point out the potential scalability problem. Thanks dav0id for agreeing my concern is valid.