Copying XML data from a SQL query


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.

  1. No comments yet.
(will not be published)