4897Re: [SQLQueriesNoCode] Base 64 to Text Cast Error
- Mar 11, 2014I am asking on this thread too:
http://stackoverflow.com/questions/22331478/sql-server-text-field-casting-error-and-base64-decodingOn Tue, Mar 11, 2014 at 11:31 AM, Charles Carroll <seniorprogrammerguru@...> wrote:So how would I integrate that into the query I showed? I am multi-tasking so am not able to get this query my full attention. I am wearing 7 or a dozen hats on this project instead of my usual 3.The "hacky ways" interest me the least. I have it working hacky ways. I am looking form a clean first calss way syntax wise. I don't mind verbose as long as it can be part of the query. I am used to obtuse and verbose in T-SQL.
On Tue, Mar 11, 2014 at 11:31 AM, John Warner <john@...> wrote:
This bites but does appear to work at least SQL doesn’t complain:
DECLARE @y varbinary(max), @x varchar(max)
SELECT @x = CONVERT(varchar(max), [CustomerDesc]) FROM [Northwind].[dbo].[CustomerDemographics]
SELECT @y = CONVERT(varbinary(max), @x)
Thing is all the conversions, haven’t actually tried to pass this through the XML functions. What you might consider is instead of my scalers, put this into #TABLES Then try your conversion from the last table. Again note I said this bites but since it is a one time thing ??
The only other thing I can think of would be write a small program to wade through the data, make the conversion, and write the result to your target table. If C# is already on the server the performance might not be too bad and shouldn’t clobber the network while it is grinding.
The base64 part of the conversion is fine in my query. It is working.
But none of the Base64 / XML functions like the Text data field type. So I am looking for a CAST or CONVERT that turns it into a type the function can deal with.
DP.Docpage is a field of HTML Documents Base64encoded.
is the base64 encoding of text
so it is a sanity check to see if our Base64 encode in T-SQL was working.
,CAST(CAST(N'' AS XML).value('xs:base64Binary("VGVzdERhdGE=")', 'VARBINARY(MAX)') AS VARCHAR(MAX)) ASCIIEncoding
works great everything hard-coded.
,CAST(CAST(N'' AS XML).value('xs:base64Binary(DP.DocPage)', 'VARCHAR(MAX)') AS VARCHAR(MAX)) ASCIIEncoding
produces the error:
XQuery [value()]: Cannot explicitly convert from 'xdt:untypedAtomic *' to 'xs:base64Binary'
,CAST(CAST(N'' AS XML).value('xs:base64Binary(' + Convert(varbinary,Dp.DocPage) + ')', 'VARBINARY(MAX)') AS VARCHAR(MAX)) ASCIIEncoding
produces the error:
Explicit conversion from data type text to varbinary is not allowed.
- << Previous post in topic