Query Anaylzer And FOR XML - Sql Server 2000: Easy

Now, here is a golden tip for those who spend hours trying to compose or troubleshoot their FOR XML queries using Query Analyzer.

All you have to do to get your XML to be formatted beautifully (well, let's just say properly since I don't see why query analyzer couldn't be smart enough to pick up on XML output and just do the right thing) is to execute DBCC TRACEON(257)! Of course, don't forget to also increase the buffer size for the result to whatever you need (max value is 8192 characters)

Credit for this tip really goes to Eron Wright since I found this out a long time ago by reading his blog. This entry is archived though so I thought I would keep it alive by posting it here as well. You can still see it though on http://geekswithblogs.net/ewright/archive/2004/09/15/11230.aspx