2010-07-02
Tricky SQL XML support for binary values
In setting up "Event" based block notifications for SQL 2005/2008 I had to get the binary SQL handle out of the XML provided by the event. This of course seems rather simple, except you can't just supply varbinary(64) as the type to @xml.value because that would too easy for an MS product. Trying this gives you back NULL instead of your binary value.
As this page tells you, you need to use an XQuery conversion, xs:hexBinary, to do instead. But wait, you're not off the hook yet, because xs:hexBinary doesn't understand 0x delimited binary, and doesn't tell you that in any way shape or form. Instead you get back your old buddy NULL. And that's when you notice buried in the XQuery that it's actually chopping off the 0x if the value has it (even though the test value in the code doesn't).
So the whole chunk you end up needing looks like this:
Process.value( 'xs:hexBinary( substring((frame/@sqlhandle)[1],3))', 'varbinary(64)' )
