SQL Server XML Query formatting

Question

When I query SQL Server with a FOR XML statement, I get the expected the result in the following format...that is without proper indentation ---

<ROOT><SUBROOT><A>1111</A></SUBROOT><SUBROOT><B>2222</B></SUBROOT><ROOT>  

How do I format this result or query it to get in the following format?

<ROOT>
 <SUBROOT>
  <A>1111</A>
 </SUBROOT>
 <SUBROOT>
  <B>2222</B>
 </SUBROOT>
<ROOT>

Answer 1

When you run your query in GRID mode, the XML column is hyperlinked.
The hint appears when you hover over it.
Click it as shown to reveal a formatted XML tab.

Answer 2

SQL Server doesn't have any way to "tidy" XML results. You'll need to use a third-party tool to do that. NotePad++ has some XML formatting tools that do well, as long as you're not working with ridiculously large XML (aka 100MB) files.

If you just want a really basic formatting, you can do a string replacement of >< with >\n< or something like that with your front-end. Your performance will be EXTREMELY poor trying to do any sort of formatting of the XML in SQL Server. DO NOT attempt to format your XML on SQL Server. Ever.

If you're running a .Net front-end, you can try plugging in something like TidyForNet to pretty up the XML, or you can run it through an XSLT transform (not preferred, IMHO).

^{NOTE: If you just need to run a query once and view the resultant XML in "tidy" format, check RichardTheKiwi's answer.}