Convert HEX to Text

I have an image data type in a table (it is a digital signature) and one of my users wants me to search the image data and see how many people have a certain attribute in their digital signature. I know the image data is HEX, but how, in query analyzer, can I view it as the XML digital signature data that it really is? So, how can I convert hex to text in sql server?

hi
you can use this function:

CREATE function HEXTODEC(@.s VARCHAR(255) )
--Converts an hexadecimal number to decimal.
returns int
as
BEGIN
DECLARE @.i int, @.temp char(1), @.result int
SELECT @.i=1
SELECT @.result=0
WHILE (@.i<=LEN(@.s))
BEGIN
SELECT @.temp=UPPER(SUBSTRING(@.s,@.i,1))
IF (@.temp>='0') AND (@.temp<='9')
SELECT @.result=@.result+ (ASCII(@.temp)-48)*POWER(16,LEN(@.s)-@.i)
ELSE
IF (@.temp>='A') AND (@.temp<='F')
SELECT @.result=@.result+ (ASCII(@.temp)-55)*POWER(16,LEN(@.s)-@.i)
SELECT @.i=@.i+1
END
return @.result
END

good luck

|||

Thanks, but that doesn't help. I had actually found that on a Google search, but it only works for numbers, when the data is really XML.

I did, however, find that SQL Manager 2005 for SQL Server can show the image field as the XML that it really is, so I was able to kind of get the data out that I needed. A bit of a convoluted way, and I only have a trial version of SQL Manager 2005 for SQL Server, but it got me the results I needed.