Encode and decode XML strings in base64

Question

I just got a job working mainly with SQL Server 2016 (v13.0.5).

The task I'm working on is about taking a column of XML strings that are currently encoded in base64, and finding a way to decode this, change it, and encode it again through straight SQL or things like stored procedures - as we mainly make forms with this data in Report Builder and don't connect with web scripts or other programs, using something else than SQL for this (Python has been suggested) would be adding an extra cog to the system, which is not welcomed. So please don't give me a hard time for looking for this, I know it's not optimal.

I have looked at several other posts here. The encoding function offered as a solution in Microsoft SQL 2016 decode Base64 column gives me a column full of nulls. This other one Base64 encoding in SQL Server 2005 T-SQL is meant for converting a single string, and it seems I'm not experienced enough with SQL in general to try and embed this in a stored procedure or something to go through the column one value at a time. Here's my last attempt, which just does not change anything.

SET NOCOUNT ON;  

DECLARE @xmlvalue varchar(200)

DECLARE pruebacursor CURSOR FOR
    SELECT xmlvalue FROM base64pruebas

OPEN pruebacursor

FETCH NEXT FROM pruebacursor INTO @xmlvalue

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE base64pruebas 
    SET XMLValue = (CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@xmlvalue"))', 'VARCHAR(MAX)'))
    WHERE xmlvalue = @xmlvalue

    FETCH NEXT FROM pruebacursor INTO @xmlvalue
END

CLOSE pruebacursor;
DEALLOCATE pruebacursor;

At the end of this, the column in question is still the same as when I started.

Thank you for any suggestions on how to improve this.

It would help if you could post what the data looks like, and what the expected output should be. — Niels Berglund, Apr 29 '21 at 17:11
Well... The data is `````` but with actual stuff inside, and I need the output to be "PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTE2Ij8+PHJwdHNldHQgeG1sbnM6eHNkPSJodHRwOi8vd3d3LnczLm9yZy8yMDAxL1hNTFNjaGVtYSIgeG1sbnM6eHNpPSJodHRwOi8vd3d3LnczLm9yZy8yMDAxL1hNTFNjaGVtYS1pbnN0YW5jZSIgbj0ibmFtZSI+PHJwdHM+PC9ycHRzPjwvcnB0c2V0dD4=". And the other way around. — Trisha, Apr 30 '21 at 11:10
SQL can do a lot, but your goals may be easier to achieve with a programming language. Since SQL Server is a Microsoft product, the .NET Framework and C# or VB.NET may help. Also consider searches on "ETL" (Extract Transfer and Load); that type of application may be what you want. — JohnH, May 01 '21 at 12:22

score 0 · Accepted Answer · answered Apr 30 '21 at 13:24

The xs:base64Binary() trick you're attempting to use requires varbinary(max) source data in order to generate base64 encoded output. The cursor loop in your example code is also unnecessary.

Try something like this...

--
-- Setup example data...
--
create table dbo.base64pruebas (
  xmlvalue varchar(max)
);

insert dbo.base64pruebas (xmlvalue) values
  (N'<?xml version="1.0" encoding="utf-16"?><rptsett xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" n="name"><rpts></rpts></rptsett>');

--
-- Base64 encode the "xml" data...
--
update base
set xmlvalue = cast('' as xml).value('xs:base64Binary(sql:column("binaryValue"))', 'varchar(max)')
from dbo.base64pruebas base
outer apply (
  select [binaryValue] = cast(xmlvalue as varbinary(max))
) as conv;

--
-- Display the results...
--
select * from dbo.base64pruebas;

Which yields the output...

xmlvalue
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTE2Ij8+PHJwdHNldHQgeG1sbnM6eHNkPSJodHRwOi8vd3d3LnczLm9yZy8yMDAxL1hNTFNjaGVtYSIgeG1sbnM6eHNpPSJodHRwOi8vd3d3LnczLm9yZy8yMDAxL1hNTFNjaGVtYS1pbnN0YW5jZSIgbj0ibmFtZSI+PHJwdHM+PC9ycHRzPjwvcnB0c2V0dD4=

This worked, thank you so much. With this I should be able to figure out how to arrange the reverse as well. — Trisha, May 06 '21 at 10:28
This [other answer](https://stackoverflow.com/a/67342001/390122) should help you to figure that part out, too. Good luck! — AlwaysLearning, May 06 '21 at 10:32

Encode and decode XML strings in base64

1 Answers1