Pretty-print XML result - db<>fiddle

Jack Douglas replying to Paul White

that's neat

Paul White

In any case, you should try the original form with more than 2033 characters to ensure the result looks ok

Paul White

Depends how much SQL Server on db<>fiddle wants to emulate the existing SQL Server tools

Paul White

Or use the `TYPE` directive https://dbfiddle.uk/G4ZyrGCo?hide=2

Paul White

https://techcommunity.microsoft.com/t5/sql-server-blog/what-does-server-side-for-xml-return/ba-p/383062

Paul White

> For maximum XML publishing performance FOR XML does steaming XML formatting of the resulting rowset and directly sends its output to the server side TDS code in small chunks without buffering whole XML in the server space. The chunk size is 2033 UCS-2 characters. Thus, XML larger than 2033 UCS-2 characters is sent to the client side in multiple rows each containing a chunk of the XML.
SQL Server uses a predefined column name for this rowset with one column of type NTEXT - “XML_F52E2B61-18A1-11d1-B105-00805F49916B” – to indicate chunked XML rowset in UTF-16 encoding. This requires special handling of the XML chunk rowset by the APIs to expose it as a single XML instance on the client side. In ADO.Net, one needs to use ExecuteXmlReader, and in ADO/OLEDB one should use the ICommandStream interface.

Paul White

@Jack @PeterVandivier  The special column name means it should be interpreted as xml

PeterVandivier

or maybe this just stays a power user feature for now :-P

PeterVandivier

i wonder if there's an unobtrusive dismissible element or something you could add to clue users into "_i thought it was XML, but it's really ntext_" issue to make the feature more discoverable?

PeterVandivier

<>https://dbfiddle.uk/C-mVFASS?hide=2

PeterVandivier

love it!

Jack Douglas

let me know what you think :)

Jack Douglas

I've tweaked just the 2017 engine for testing: https://dbfiddle.uk/9dreK4OO?hide=8

Jack Douglas

the data type from your query seems to be `ntext` rather than `xml` - but if yo uare happy to cast into `xml` I think it's ok for db<>fiddle to pretty print that

PeterVandivier

It's an SSMS perk. No built-in TSQL support AFAIK

Jack Douglas

@PeterVandivier, can you get pretty printed XML in TSQL, or is it only in SSMS?

1 Answer