Solved

Converting base64Binary to UTF-8 (String) in SQL Server 2008

Posted on 2016-09-13
27
56 Views
Last Modified: 2016-09-22
Heyas,

I have the following query:


SELECT bd.data_timestamp
       ,bd.*
       ,wbd.*
       ,CAST( CAST(wbd.DATA_TYPE as XML ).value('.','varbinary(max)') AS varchar )

FROM MSGS bd, WMB_data_event wbd
WHERE bd.MSGFLOW_NAME = 'TestDataFlow'
       AND bd.data_timestamp > '2016-01-10 13:30:00'
       AND bd.data_timestamp < '2016-01-10 18:30:00'
       AND bd.TERMINAL_NAME = 'pass'
       AND bd.WMB_MSGKEY = wbd.WMB_MSGKEY
       AND wbd.DATA_TYPE = 10
ORDER BY bd.data_timestamp ASC

I am trying to Convert base64Binary to UTF-8 on the column 'wbd.DATA_TYPE' however when I run the query I get the following problem.

Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type int to xml is not allowed.

How do I resolve this issue any assistance is appreciated.

Thank you.
0
Comment
Question by:Zack
  • 13
  • 6
  • 3
  • +2
27 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41797353
Try below

SELECT bd.data_timestamp
       ,bd.*
       ,wbd.*
       ,CAST(CAST(wbd.DATA_TYPE as sql_variant ).value('.','varbinary(max)') AS varchar(max) )

FROM MSGS bd, WMB_data_event wbd
WHERE bd.MSGFLOW_NAME = 'TestDataFlow'
       AND bd.data_timestamp > '2016-01-10 13:30:00'
       AND bd.data_timestamp < '2016-01-10 18:30:00'
       AND bd.TERMINAL_NAME = 'pass'
       AND bd.WMB_MSGKEY = wbd.WMB_MSGKEY
       AND wbd.DATA_TYPE = 10

ORDER BY bd.data_timestamp ASC

Open in new window

0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41797354
Also can you send a row which is giving error ?
0
 

Author Comment

by:Zack
ID: 41797372
Hi Pawan,

Your code gives me the error:

Msg 258, Level 15, State 1, Line 5
Cannot call methods on sql_variant.

There is no row I know of which is giving me the error.

Thank you.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41797378
Could you please send few rows  from column wbd.DATA_TYPE in a excel file, so that i can review.?
0
 

Author Comment

by:Zack
ID: 41797382
Hi Pawan,

Can't do that as the data itself is 'insurance data' hence a little sensitive. I can tell you the data can be decoded successfully using the 'https://www.base64decode.org/' site.

Let me know if there is anything else you would like to know about the data, though.

Thank you.
0
 

Author Comment

by:Zack
ID: 41797385
Hi Pawan,

Attached is file showing the column properties in SQL server.

Thank you.
DATA_TYPE-Columns.PNG
0
 

Author Comment

by:Zack
ID: 41797406
Hi Pawan,

Using the function found on this web page:

http://www.jrevell.com/base64-encoding-and-decoding-with-sql-server-unicode-characters/

Convert from Base64

CREATE FUNCTION [dbo].[fn_str_FROM_BASE64]
(
    @BASE64_STRING NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN (
        SELECT 
            CAST(
                CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@BASE64_STRING"))', 'VARBINARY(MAX)') 
            AS NVARCHAR(MAX)
            )   UTF8Encoding
    )
END

Open in new window


My query below:

SELECT bd.data_timestamp
       ,bd.*
       ,wbd.*
       ,MB.dbo.fn_str_FROM_BASE64(wbd.DATA_TYPE) AS Converted_Value
FROM MSGS bd, WMB_data_event wbd
WHERE bd.MSGFLOW_NAME = 'TestDataFlow'
       AND bd.data_timestamp > '2016-01-10 13:30:00'
       AND bd.data_timestamp < '2016-01-10 18:30:00'
       AND bd.TERMINAL_NAME = 'pass'
       AND bd.WMB_MSGKEY = wbd.WMB_MSGKEY
       AND wbd.DATA_TYPE = 10

ORDER BY bd.data_timestamp ASC

Open in new window


The 'Converted Value' returns a null value if this provides further insights.

Thank yo.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41797451
Great ! can you please mark one solution as accepted solution and close it..
0
 

Author Comment

by:Zack
ID: 41797452
Hi Pawan,

No solution yet : The 'Converted Value' returns a null value if this provides further insights.

Let me know if you think of anything.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41797459
At least could you please one value of wbd.DATA_TYPE which is getting converted to NULL value.
0
 

Author Comment

by:Zack
ID: 41797471
HI Pawan,

Generated some test output hopefully the format helps:

C01TSHxeflwmfEFJU1RFQ0h8T0N8V0laQVJEfFRFU1RDRU5UUkV8MjAxNjA5MTEwMjAxMjh8U0VD
VVJFfERGVF5QMDN8UlhDLTAwMTUwNjg5NDl8UHwyLjINCkVWTnxQMDN8MjAxNjA5MTEwMjAxMjgN
ClBJRHwxfDc4OTQ1NDB8NDUzNjcyNDM0fDg5NzIzNDYtRklOfFNUIFphY2teQnJvd258fDE5OTAw
ODAyMDAwMDAwMDB8TXx8fHx8fHx8fHw3ODk0NTQwDQpQQjJ8fHxURVNUVVNFUnx8fHwoMjM2MzY0
QkIpIEFsZXgNCkZUMXx8Nzg0MDM4ODl8fDIwMTYwOTExMDIwMTI2MDB8fEl8T05EQTMwMnx8fDF8
fHx8fHx8fHx8QUFNMzIxNA0KWlBNfFZ8QUJDREV8TUFUUklYfCB8MTA0MXxPTkRBMzAyfE9NTklU
RVNUREFUQXw2fCB8MjcuMDB8MS4wMHw4NzQ2NjU0MzZ8QUFNMzIxNHwgfCB8MjcuMDB8IHwgfCB8
VEVTVFVTRVJ8IHw0MHwxMHwyMDE2MDkxMTAyMDEyNjAwDQpaUEJ8UmVmZXJhbHw4Nzk0NTQzNDN8
OCBIUkxZIChTRVFVRU5DRSl8UE98dGFiLWRpcw==

Thank you
0
 

Author Comment

by:Zack
ID: 41797472
Hi Pawan,

The above is getting a NULL value.

Thank you.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41797473
wait let me check .
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 26

Expert Comment

by:Zberteoc
ID: 41797929
I tried the function fn_str_FROM_BASE64 with the Base64 string that was pested and I got some chinese text:

䴋䡓幼屾簦䥁呓䍅籈䍏坼婉剁籄䕔呓䕃呎䕒㉼㄰〶ㄹ〱〲㈱簸䕓啃䕒䑼呆偞㌰剼䍘〭㄰〵㠶㐹簹籐⸲ല䔊乖偼㌰㉼㄰〶ㄹ〱〲㈱സ倊䑉ㅼ㝼㤸㔴〴㑼㌵㜶㐲㐳㡼㜹㌲㘴䘭义卼⁔慚正䉞潲湷籼㤱〹㠰㈰〰〰〰〰䵼籼籼籼籼籼㠷㐹㐵ര倊㉂籼呼卅啔䕓籒籼⡼㌲㌶㐶䉂
汁硥਍呆簱㝼㐸㌰㠸簹㉼㄰〶ㄹ〱〲㈱〶簰䥼佼䑎㍁㈰籼ㅼ籼籼籼籼籼䅁㍍ㄲഴ娊䵐噼䅼䍂䕄䵼呁䥒籘簠〱ㄴ佼䑎㍁㈰佼乍呉卅䑔呁籁簶簠㜲〮簰⸱〰㡼㐷㘶㐵㘳䅼䵁㈳㐱⁼⁼㉼⸷〰⁼⁼⁼呼卅啔䕓籒簠〴ㅼ簰〲㘱㤰ㄱ㈰㄰㘲〰਍做籂敒敦慲籬㜸㐹㐵㐳簳‸剈奌⠠䕓啑久䕃簩佐瑼扡搭獩

I am using SQL 2012, the asker is using SQL 2008. It might be a version issue?

Awsome functions, by the way!
0
 

Author Comment

by:Zack
ID: 41798980
Hi Zberteoc,

Cheers for that although they are not mine. Thank Google.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41799841
What do you mean they are not yours? I copied the Base64 text you posted above and that is what the function returned wen applied to it, the Chinese text while you were complaining it returned NULL. Can you explain what you meant?
0
 

Author Comment

by:Zack
ID: 41800737
Hi Zbertoc,

The base64 text is what I am trying to read that's mine. The function though I am using isn't check the link:  http://www.jrevell.com/base64-encoding-and-decoding-with-sql-server-unicode-characters/

Thank you.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41801542
But is it returning a NULL value for that Base64 string or not?
0
 

Author Comment

by:Zack
ID: 41803026
Hi Zbertoc,

Yes it's returning a NULL value for the Base64 string.

Thank you.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 41810092
Well... you are going to have some problems with UTF-8 because SQL doesn't know about that, but converting from base64 is easy enough. You might be getting NULLS because of zero byte Unicode, or, non-ASCII characters within the base64 encoding.

Using your sample above...

declare @str varchar(max);   -- could be nvarchar

set @str = 'C01TSHxeflwmfEFJU1RFQ0h8T0N8V0laQVJEfFRFU1RDRU5UUkV8MjAxNjA5MTEwMjAxMjh8U0VD
VVJFfERGVF5QMDN8UlhDLTAwMTUwNjg5NDl8UHwyLjINCkVWTnxQMDN8MjAxNjA5MTEwMjAxMjgN
ClBJRHwxfDc4OTQ1NDB8NDUzNjcyNDM0fDg5NzIzNDYtRklOfFNUIFphY2teQnJvd258fDE5OTAw
ODAyMDAwMDAwMDB8TXx8fHx8fHx8fHw3ODk0NTQwDQpQQjJ8fHxURVNUVVNFUnx8fHwoMjM2MzY0
QkIpIEFsZXgNCkZUMXx8Nzg0MDM4ODl8fDIwMTYwOTExMDIwMTI2MDB8fEl8T05EQTMwMnx8fDF8
fHx8fHx8fHx8QUFNMzIxNA0KWlBNfFZ8QUJDREV8TUFUUklYfCB8MTA0MXxPTkRBMzAyfE9NTklU
RVNUREFUQXw2fCB8MjcuMDB8MS4wMHw4NzQ2NjU0MzZ8QUFNMzIxNHwgfCB8MjcuMDB8IHwgfCB8
VEVTVFVTRVJ8IHw0MHwxMHwyMDE2MDkxMTAyMDEyNjAwDQpaUEJ8UmVmZXJhbHw4Nzk0NTQzNDN8
OCBIUkxZIChTRVFVRU5DRSl8UE98dGFiLWRpcw==
';

select convert(varchar(max),cast(N'' as xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(max)'));

Open in new window


And gives the result :

MSH|^~\&|AISTECH|OC|WIZARD|TESTCENTRE|20160911020128|SECURE|DFT^P03|RXC-0015068949|P|2.2
EVN|P03|20160911020128
PID|1|7894540|453672434|8972346-FIN|ST Zack^Brown||1990080200000000|M||||||||||7894540
PB2|||TESTUSER||||(236364BB) Alex
FT1||78403889||2016091102012600||I|ONDA302|||1||||||||||AAM3214
ZPM|V|ABCDE|MATRIX| |1041|ONDA302|OMNITESTDATA|6| |27.00|1.00|874665436|AAM3214| | |27.00| | | |TESTUSER| |40|10|2016091102012600
ZPB|Referal|879454343|8 HRLY (SEQUENCE)|PO|tab-dis

Open in new window


So, to test, set your @str to a selected (single) instance from your table, and run the xquery.

If you can let me know your results... We can then modify the query as appropriate and use the sql:column in the xquery instead of sql:variable (and maybe the use of Unicode) But base64 is meant to be ascii in nature.
0
 

Author Comment

by:Zack
ID: 41810150
Hi Mark,

That query you gave me works perfectly when you able kindly show me to modify the original query to give the expected results.

Thank you.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 41810189
Try this (have to go out so not tested)... swap the column name and the table name (ie "t.your_64_column" and "your_table as t")

select convert(varchar(max),cast(N'' as xml).value('xs:base64Binary(sql:column("t.your_64_column"))', 'varbinary(max)')) as converted_64_column
from your_table as t

Open in new window


I can retrofit your query when I get back...
0
 

Author Closing Comment

by:Zack
ID: 41810225
Thank you for the help that query works (Very slowly) and I adapted it to original query.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41810488
@Mark

Very clever.  If speed is an issue, could a temp table solve the performance issue?
0
 

Author Comment

by:Zack
ID: 41810646
Hi Aikimark,

Good point I will try that tomorrow.

Cheers.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 41810809
Yeah, it can be a bit of a drag in terms of performance, so best to make sure you get only those rows that you really want first. A temp table might help, but would probably try a CTE or subquery to first  get the data (with those where clauses) then do the xquery part in a final select.

Also noticed that there isn't a join between the two tables, so that will impact a bit more. And indexing, and predicates, and, and...

But very happy that it is working for you and apologies for running out like that. My daughters valedictory (black tie) dinner tonight... It was a pretty big deal for her final year of school.
1

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now