Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-09-13
27
Medium Priority
?
1,123 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
26 Comments
 
LVL 35

Expert Comment

by:Pawan Kumar
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 35

Expert Comment

by:Pawan Kumar
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 35

Expert Comment

by:Pawan Kumar
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 35

Expert Comment

by:Pawan Kumar
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 35

Expert Comment

by:Pawan Kumar
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 35

Expert Comment

by:Pawan Kumar
ID: 41797473
wait let me check .
0
 
LVL 27

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 27

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 27

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 2000 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 46

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

824 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