Data blobs are xml that have been base64 need to be decoded and stored

Question

Based on my requirement, we have data blobs are xml that have been base64 encoded, need to be decoded and stored in tables which must be in readable format.

Can this be done in SQL/MYSQL/TSQL?

I need to pull the thus obtained tables on to our BI tool and create reports. Our BI tool doesn't support data blobs which are xml format.

When asking a question, try to be as specific as possible. a base64 encode is basically a VARCHAR(size) field, with letters of the `64` base char concatenated. So... what is the issue you are having? — Bonatti, Mar 24 '16 at 18:14
I am trying to fetch the data from My SQL Database on to our BI Tool. One of the tables have a field Public whose data type is Blob. The BI Tool I am using does not support BLOB data unless they are .jpg, .png, .bmp, .rtf data formats. When I checked back with my business to find out the data type they replied me that data blobs are xml that have been base64 encoded. At this point this is the only information I have. — jarus, Mar 24 '16 at 18:34
Again, you need to specify your problem: `Can this be done in SQL/MYSQL/TSQL?` Yes. Its a VARCHAR (or some other `String-like` structure) already. If they are saved as Blob, then it is a binary state of the bits/Bytes from the original archive. You have to query that, then read the file as needed. XML files are usually "key":"value" pairs, that allow some context. Since these are non-linear, it is unlikely you can read them directly from SQL to "table" of results. Even if someone knows the answer, the question is so broad/uncertain that is hard to help you. — Bonatti, Mar 24 '16 at 18:41

score 0 · Accepted Answer · edited May 23 '17 at 11:59

This is a solution with T-SQL:

Credits to: https://stackoverflow.com/a/32231832/5089204

DECLARE @x XML='<root><Test name="checkTest">TestValue</Test></root>';
SELECT @x;

--This is the XML string in base64
DECLARE @xBase64 VARCHAR(MAX)='PHJvb3Q+DQogIDxUZXN0IG5hbWU9ImNoZWNrVGVzdCI+VGVzdFZhbHVlPC9UZXN0Pg0KPC9yb290Pg==';

CREATE TABLE #testBase64(ID INT, SomeBLOB VARBINARY(MAX));
INSERT INTO #testBase64 VALUES(1,CAST(@xBase64 AS VARBINARY(MAX)));

SELECT ID 
      ,SomeBLOB                          AS TheBLOB_as_HexString
      ,CAST(SomeBLOB AS VARCHAR(MAX))    AS TheBLOB_back_to_Base64

      --taken from here: https://stackoverflow.com/a/32231832/5089204
      ,CAST(CAST(CAST(CAST(SomeBLOB AS VARCHAR(MAX)) AS XML).value('.','varbinary(max)') AS VARCHAR(MAX)) AS XML) AS Back_to_XML

FROM #testBase64;

DROP TABLE #testBase64;

/*
    The results
    Hex-String: 0x50484A766233512B44516F67494478555A584E304947356862575539496D4E6F5A574E725647567A6443492B5647567A64465A686248566C504339555A584E305067304B504339796232393050673D3D
    Base64:     PHJvb3Q+DQogIDxUZXN0IG5hbWU9ImNoZWNrVGVzdCI+VGVzdFZhbHVlPC9UZXN0Pg0KPC9yb290Pg==
    XML again:  <root><Test name="checkTest">TestValue</Test></root>
*/

Data blobs are xml that have been base64 need to be decoded and stored

1 Answers1