What I'm trying to do:
we have a stored procedure called from a custom app. Pulls off a Queue, then runs the stored procedure using the contents of the Queue (which is XML).
When I have 1, it's really easy:
declare @msg varbinary
set @msg = 0x300000000134985709sdg8fs0d9f8fa0s7df90 (which would be hex in the real world)
select convert(xml, @msg)
However, I needed to do that for several hundred rows.
So, the conversion from the native to varbinary(max) was failing - if you CAST or CONVERT it, it actually seems to CHANGE it... which causes the field to no longer be proper XML. And so the CAST to XML fails.
And thus.... the fix is..... Dynamic SQL (yeah, I cringed too)
[...code to extract the varbinary string removed - multiple CTEs]
create table finished_trace (data_value xml)
DECLARE @min int, @max INT, @sql NVARCHAR(MAX)
SELECT @min = MIN(event_number) , @max = MAX(event_number) FROM #t3
WHILE @min <= @max
BEGIN
SET @sql = NULL
SELECT @sql = 'declare @msg varbinary(max)
set @msg = ' + data_value + '
select convert(xml, @msg)' FROM #t3 WHERE event_number = @min
INSERT INTO finished_trace (data_value)
EXEC (@sql)
SET @min = @min + 1
end
