In the value() method post of my XQuery for the Non-Expert series I discussed how to take element and attributes and return them as column values.  Those methods works all fine and dandy for most data but when it comes to binary data things get a little muddled.

In this post, we’ll look at the issue that occurs when you try to return binary data from an XML data.  Then we’ll look at the how to use xs:hexBinary to

Setup the Data

To experience this first hand, we need some data to work with.  For simplicity sake, let’s look at an XML document with binary data in it and also a table with binary data.  The plan will be to demonstrate how to properly extract binary data from the XML document.

Here’s the demo setup script:

IF OBJECT_ID('tempdb..#ValueLookup') IS NOT NULL
DROP TABLE #ValueLookup
GO

CREATE TABLE #ValueLookup
(
SQLHandle varbinary(64)
)
GO

INSERT INTO #ValueLookup
VALUES (0x030004003029C107318723014C9D00000100000000000000)
GO

INSERT INTO #ValueLookup
VALUES (0x03000E0013AD2077378ECD00559E00000100000000000000)
GO

INSERT INTO #ValueLookup
VALUES (0x03000E008223495F208ECD00559E00000100000000000000)
GO

INSERT INTO #ValueLookup
VALUES (0x03000E009CB1BA263E8ECD00559E00000100000000000000)
GO

IF OBJECT_ID('tempdb..#ValueExample') IS NOT NULL
DROP TABLE #ValueExample
GO

CREATE TABLE #ValueExample
(
XMLDocument xml
)

INSERT INTO #ValueExample
VALUES ('




')

SELECT SQLHandle FROM #ValueLookup
SELECT XMLDocument FROM #ValueExample

In the script the table #ValueLookup has a four binary values that I pulled out of my SQL Server plan cache.  The table #ValueExample has those same values in an XML document.  The goal of our upcoming demonstrations will be to match the table values to the XML values.

Bad Binary Data

Now that we have some data to look at, let’s use the value() method to return the binary data as a string.  If you followed that how to do this in the previous post, your query would look something like this:

SELECT c.value('@value','varchar(max)') BadSQLHandle
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Data/SQLHandle') as t(c)

And your output will be the as such:

These look about right.  We’ll test them out to see what happens when they are joined to the binary data in the #ValueLookup table.  The query I used to do this is:

;WITH SQLHandle
AS (
SELECT c.value('@value','varchar(max)') BadSQLHandle
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Data/SQLHandle') as t(c)
)
SELECT *
FROM SQLHandle s
INNER JOIN #ValueLookup v ON s.BadSQLHandle = v.SQLHandle

And the results of which are:

Not exactly what we were looking for.  What happens if we go straight to binary data with the value() method.  That would be this query:

SELECT c.value('@value','varbinary(max)') BadSQLHandle
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Data/SQLHandle') as t(c)

In the results we get all NULL values.

Obviously, that didn’t work.  We could also try to CAST the results of value() method in the first query as binary.  That would at least guarantee that the values are binary.  Here’s the query to try that:

SELECT CAST(c.value('@value','varchar(max)') as varbinary(max)) BadSQLHandle
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Data/SQLHandle') as t(c)

And as expected since this is the bad section, we get binary data returned.  The data returned is binary but the values are are not the same as before they were the CAST.

Good Binary Data

Now that I’ve successfully failed three times in a row, let’s move on to the way to accomplish this task.  As I mentioned in the introduction we will need to use the hexBinary() function.

The hexBinary() function accepts a single parameter.  This is the value that needs to be converted.  Also, because we are working with changing string to binary data the “0x” prefix needs to be removed using the substring() function.

By putting this all together, the value() method in the query gets changed to:

SELECT c.value('xs:hexBinary(substring(@value,3))','varbinary(64)') GoodSQLHandle
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Data/SQLHandle') as t(c)

Running this query returns a result set similar to the one from the first query:

I say similar because if the results are joined to the #ValueLookup table with a query like this:

;WITH SQLHandle
AS (
SELECT c.value('xs:hexBinary(substring(@value,3))','varbinary(64)') GoodSQLHandle
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Data/SQLHandle') as t(c)
)
SELECT *
FROM SQLHandle s
INNER JOIN #ValueLookup v ON s.GoodSQLHandle = v.SQLHandle

You’ll find that the data is actually binary data and it can be joined to the other binary data in other tables.  See the results below:

hexBinary Wrap-up

If you are looking to extract binary data from your XML documents, this post should get you through the hurdles that you are likely to come across.  Being able to extract data in its true data type can be invaluable.  Especially in cases where you need to join that data back to other binary data.

Stay tuned to the Can You Dig It series where this function will probably be showing up later this week.  This post is specifically a preparation for one of those posts.

References

Converting from Base64 to varbinary and vice versa