Retrieving data stored as image as string in SQL Server 2005

Question

I need to retrieve an xml file stored as image data in SQL Server.

I am using this query -

select 
    convert(varchar, convert(binary, zd.validcontent)) 
from 
    zonedata zd
join 
    contentitem ci on zd.itemid = ci.itemid
where 
    id = @dpathid 

I get text but but the result returns only a small portion of the xml file -

<?xml version="1.0" encoding="

Please help. Thanks.

Answer 1

Possible this be helpful for you -

SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), zd.validcontent))
FROM zonedata zd
JOIN contentitem ci ON zd.itemid = ci.itemid
WHERE ID = @dpathid