- 8 Week Game
- Quick Ref
Archive for August, 2012
We have an older system which stores XML as a varchar in the database. Copying & pasting from this often truncates the content, and if it doesn;t you then have to paste it into visual studio or something to get the correct formatting etc etc…. it’s a pain.
A quick way round this- if you’re using SQL server 2005 or above- is to cast the output as xml- this makes the xml like a clickable hyperlink in the results panel, and clicking it opens up the full unabridged version of the xml, nicly formatted and indented as god intended.
SELECT TOP 100 [ID] ,cast(replace(request,'encoding="UTF-8"','encoding="UTF-16"') as xml) ,cast(replace(response,'encoding="UTF-8"','encoding="UTF-16"') as xml) ,[UserID] ,[RequestDate] FROM [MyXMLDataLogTable] order by id desc
Thanks go once more to my good colleague Andrew Carmichael for this top tip.