Loading ...
Sorry, an error occurred while loading the content.

4896Re: [SQLQueriesNoCode] Base 64 to Text Cast Error

Expand Messages
  • Charles Carroll
    Mar 11, 2014
      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)

      SELECT @y


      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.


      John Warner


      From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Charles Carroll
      Sent: Tuesday, March 11, 2014 9:53 AM
      To: SQLQueriesNoCode@yahoogroups.com

      Subject: Re: [SQLQueriesNoCode] Base 64 to Text Cast Error


      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.


    • Show all 12 messages in this topic