Reputation: 1336
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.
Background: I've run a trace across one of our servers that receives XML bundles. The way it's called looks like:
exec sp_executesql N'EXEC dbo.myproc @MSG',N'@MSG varbinary(max) ',@MSG=0xFFFE3...
sample repro:
DECLARE @msg XML = '<A>
<B>
<C>1</C>
</B>
</A>'
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
So now I need to extract the @MSG and view the XML.
SELECT STUFF(CAST(textdata AS NVARCHAR(max)),1,71,'') FROM faketrace
That gives me the 0xFFFE...
But how do I convert the field back to XML?
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.
DECLARE @min int, @max INT, @sql NVARCHAR(MAX)
SELECT @min = MIN(id) , @max = MAX(id) FROM faketrace
WHILE @min <= @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
Upvotes: 0
Views: 533
Reputation: 2908
SELECT
textdata,
CAST(textdata AS NVARCHAR(max)),
-- use SUBSTRING instead of STUFF, but LEN won't take an NTEXT, so cast to NVARCHAR
SUBSTRING(textdata, 72, LEN(CAST(textdata AS NVARCHAR(max))) - 71),
-- you can use CONVERT with the third argument of 1
CONVERT(VARBINARY(MAX), SUBSTRING(textdata, 72, LEN(CAST(textdata AS NVARCHAR(max))) - 71), 1),
-- now that you have a valid VARBINARY, you can directly cast to XML
CAST(CONVERT(VARBINARY(MAX), SUBSTRING(textdata, 72, LEN(CAST(textdata AS NVARCHAR(max))) - 71), 1) AS XML)
FROM faketrace
varbinary to string on SQL Server
https://msdn.microsoft.com/en-us/library/ms187928.aspx
Upvotes: 1