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>

Behead

Where do you get this result? SQL Server Mgmt Studio shows the one-line version in the result grid - but if you click on it, a separate XML editor opens up with the format you're looking for! – Extension

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.

Elgon

+1 - sensible solution if you need to just run a query via SSMS and view "tidy" XML. – Lipman

The image can not be loaded! – Serpentiform

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.}

Lipman