I have a column in a sql server 2005 database that stores xml as varbinary, the value lookes something like:
0x3C3F54657374696E672075706C6F616 // but much longer
Is there an online conversion tool for converting this to a readable string?
Add a comment
|
2 Answers
select CAST(0x3C3F54657374696E672075706C6F61643F3E3C666F6F3E3C2F666F6F3E as XML)
appears to work.
And as it seems your datatype is image...
;with t(c) as
(
select CAST(0x3C3F54657374696E672075706C6F61643F3E3C666F6F3E3C2F666F6F3E as IMAGE)
)
select CAST(CAST(c as VARBINARY(MAX)) as XML)
from t
Comments
You can use the undocumented stored procedure.
SELECT fn_varbintohexstr(@YourVarbar)
You should also be able to just cast it to a varchar(max) as well
Select CAST(@YourVarbar as Varchar(max))
Third alternative is to create an XML document out of it and use forXML Example at the following location http://beyondrelational.com/blogs/jacob/archive/2009/06/13/converting-varbinary-to-varchar-using-for-xml.aspx
DECLARE @x VARBINARY(10)
SELECT @x = CAST('10' as VARBINARY(10))
SELECT @x AS VarBinaryValue
SELECT (
SELECT
CHAR(SUBSTRING(@x,number,1)) AS 'text()'
FROM master..spt_values
WHERE type = 'P'
AND Number BETWEEN 1 AND LEN(@x)
FOR XML PATH('')
) AS TextValue
1 Comment
Peter
Hi, Thanks, I tried with your first example and got "Explicit conversion from data type image to varchar(max) is not allowed."