Thursday, October 2, 2014

[Extended Events] converting results of rpc_completed , originally VARBINARY, into XML

Maybe there's a better way to do it.  After futzing with various combinations for an hour, I gave up.

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