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

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.
ZackGeneral IT Goto GuyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
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
Pawan KumarDatabase ExpertCommented:
Also can you send a row which is giving error ?
0
ZackGeneral IT Goto GuyAuthor Commented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Pawan KumarDatabase ExpertCommented:
Could you please send few rows  from column wbd.DATA_TYPE in a excel file, so that i can review.?
0
ZackGeneral IT Goto GuyAuthor Commented:
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
ZackGeneral IT Goto GuyAuthor Commented:
Hi Pawan,

Attached is file showing the column properties in SQL server.

Thank you.
DATA_TYPE-Columns.PNG
0
ZackGeneral IT Goto GuyAuthor Commented:
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
Pawan KumarDatabase ExpertCommented:
Great ! can you please mark one solution as accepted solution and close it..
0
ZackGeneral IT Goto GuyAuthor Commented:
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
Pawan KumarDatabase ExpertCommented:
At least could you please one value of wbd.DATA_TYPE which is getting converted to NULL value.
0
ZackGeneral IT Goto GuyAuthor Commented:
HI Pawan,

Generated some test output hopefully the format helps:

C01TSHxeflwmfEFJU1RFQ0h8T0N8V0laQVJEfFRFU1RDRU5UUkV8MjAxNjA5MTEwMjAxMjh8U0VD
VVJFfERGVF5QMDN8UlhDLTAwMTUwNjg5NDl8UHwyLjINCkVWTnxQMDN8MjAxNjA5MTEwMjAxMjgN
ClBJRHwxfDc4OTQ1NDB8NDUzNjcyNDM0fDg5NzIzNDYtRklOfFNUIFphY2teQnJvd258fDE5OTAw
ODAyMDAwMDAwMDB8TXx8fHx8fHx8fHw3ODk0NTQwDQpQQjJ8fHxURVNUVVNFUnx8fHwoMjM2MzY0
QkIpIEFsZXgNCkZUMXx8Nzg0MDM4ODl8fDIwMTYwOTExMDIwMTI2MDB8fEl8T05EQTMwMnx8fDF8
fHx8fHx8fHx8QUFNMzIxNA0KWlBNfFZ8QUJDREV8TUFUUklYfCB8MTA0MXxPTkRBMzAyfE9NTklU
RVNUREFUQXw2fCB8MjcuMDB8MS4wMHw4NzQ2NjU0MzZ8QUFNMzIxNHwgfCB8MjcuMDB8IHwgfCB8
VEVTVFVTRVJ8IHw0MHwxMHwyMDE2MDkxMTAyMDEyNjAwDQpaUEJ8UmVmZXJhbHw4Nzk0NTQzNDN8
OCBIUkxZIChTRVFVRU5DRSl8UE98dGFiLWRpcw==

Thank you
0
ZackGeneral IT Goto GuyAuthor Commented:
Hi Pawan,

The above is getting a NULL value.

Thank you.
0
Pawan KumarDatabase ExpertCommented:
wait let me check .
0
ZberteocCommented:
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
ZackGeneral IT Goto GuyAuthor Commented:
Hi Zberteoc,

Cheers for that although they are not mine. Thank Google.
0
ZberteocCommented:
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
ZackGeneral IT Goto GuyAuthor Commented:
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
ZberteocCommented:
But is it returning a NULL value for that Base64 string or not?
0
ZackGeneral IT Goto GuyAuthor Commented:
Hi Zbertoc,

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

Thank you.
0
Mark WillsTopic AdvisorCommented:
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
ZackGeneral IT Goto GuyAuthor Commented:
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
Mark WillsTopic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZackGeneral IT Goto GuyAuthor Commented:
Thank you for the help that query works (Very slowly) and I adapted it to original query.
0
aikimarkCommented:
@Mark

Very clever.  If speed is an issue, could a temp table solve the performance issue?
0
ZackGeneral IT Goto GuyAuthor Commented:
Hi Aikimark,

Good point I will try that tomorrow.

Cheers.
0
Mark WillsTopic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.