Retrieving data stored as a string image in SQL Server 2005

Question

I need to get an xml file that is stored as image data in SQL Server.

I use 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 the text, but the result returns only a small part of the xml file -

 <?xml version="1.0" encoding=" 

Please, help. Thanks.

Answer 1

char and varchar :

char [(n)]

varchar [(n | max )]

When n is not specified in the data definition statement or variable declaration, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

So, specify a suitable length (e.g. max , as shown in @Devart's answer or any other suitable value)

Answer 2

Perhaps it will be useful 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