title: Converting a varbinary string inside NTEXT to XML using T-SQL?
body: <p>TL;DR - I am trying to convert a XML string that's encoded as a varbinary, saved as part of a trace into an NTEXT field, back to XML so I can view it.</p>
  
  <p>Background:
  I've run a trace across one of our servers that receives XML bundles.  The way it's called looks like: </p>
  
  <pre><code>exec sp_executesql N'EXEC dbo.myproc @MSG',N'@MSG varbinary(max) ',@MSG=0xFFFE3...
  </code></pre>
  
  <p>sample repro:</p>
  
  <pre><code>DECLARE @msg XML = '&lt;A&gt;
    &lt;B&gt;
      &lt;C&gt;1&lt;/C&gt;
    &lt;/B&gt;
  &lt;/A&gt;'
  SELECT  CONVERT(VARBINARY(max),@msg)
  --0xFFFE3C0041003E003C0042003E003C0043003E0031003C002F0043003E003C002F0042003E003C002F0041003E00
  USE tempdb
  GO
  CREATE TABLE faketrace (id int identity, textdata NTEXT)
  CREATE TABLE finished_trace (id int identity, data_value XML)
  GO
  INSERT INTO faketrace
          (textdata)
  VALUES  ('exec sp_executesql N''EXEC dbo.myproc @MSG'',N''@MSG varbinary(max)'',@MSG=0xFFFE3C0041003E003C0042003E003C0043003E0031003C002F0043003E003C002F0042003E003C002F0041003E00' )  -- textdata - ntext
  </code></pre>
  
  <p>So now I need to extract the @MSG and view the XML.</p>
  
  <pre><code>SELECT STUFF(CAST(textdata AS NVARCHAR(max)),1,71,'') FROM faketrace
  </code></pre>
  
  <p>That gives me the 0xFFFE...</p>
  
  <p>But how do I convert the field back to XML?</p>
  
  <p>I have a cheat way that works, but is ugly, and only does one row at a time.  I have no doubt there's a way, but every set-based version I've tried has choked.</p>
  
  <pre><code>DECLARE @min int, @max INT, @sql NVARCHAR(MAX)
  SELECT @min = MIN(id) , @max = MAX(id) FROM faketrace
  WHILE @min &lt;= @max
  BEGIN
      SET @sql = NULL
      SELECT @sql = 'declare @msg varbinary(max)
      set @msg = ' +  CAST(STUFF(CONVERT(NVARCHAR(MAX),TextData),1,71,'') AS NVARCHAR(MAX))
       + '
      select convert(xml, @msg)' FROM faketrace WHERE id = @min
      INSERT INTO finished_trace (data_value)
      EXEC (@sql)
      SET @min = @min + 1
  END
  </code></pre>