1

I do voluntary work at an animal shelter. We have an application which uses a SQL Server 2019 database. I have created a view that includes a varbinary(max) column. The value in this column is a picture, stored in hexadecimal-format. I would like to convert this Hex-value to a base64-binary file and add these to the view as an extra column.

I found the perfect solution for my situation in SQL Server : hex to base64. The example provided converts 1 single hex-value into 1 base64-value. I now need to add this solution to my view, but I'm not having any success.

The offered solution:

DECLARE @TestBinHex varchar(max), @TestBinary varbinary(max), @Statement nvarchar(max);

SELECT @TestBinHex = '0x012345';

SELECT @Statement = N'SELECT @binaryResult = ' + @TestBinHex;
EXECUTE sp_executesql @Statement, N'@binaryResult varbinary(max) OUTPUT', @binaryResult=@TestBinary OUTPUT;

SELECT
    CAST(N'' AS XML).value(
          'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
        , 'VARCHAR(MAX)'
    )   Base64Encoding
FROM 
    (SELECT @TestBinary AS bin) AS bin_sql_server_temp;

A simplified version of my view:

SELECT        
    a.cat_id, a.catname, s.cat_id, 
    s.stay_id, s.shelter_handler, s.shelter_kennel, s.picture
FROM            
    dbo.animal AS a 
OUTER APPLY
    (SELECT TOP 1 *
     FROM dbo.shelterdata
     WHERE a.cat_id = s.cat_id
     ORDER BY s.stay_id DESC) AS S
WHERE        
    (a.cat_id IS NOT NULL) AND (s.leave_date IS NULL)

The view shows an overview of all cats currently present in the shelter (leave_date is NULL). The reason for the TOP 1 is that sometimes shelter animals get returned, and the application then assigns a new stay_id. To prevent duplicate values from the join, I only return the value of the most recent stay_id.

What I am trying to achieve: the second table (dbo.shelterdata) includes the picture, stored in hex value. I'd like to add a column Base64Encoding to the view which includes the converted value.

My attempts

I was successful in replacing the static value '0x012345' by a SELECT statement. But the way the solution is formatted, it only allows for one input value. So I had to restrict it with a WHERE clause. It is obvious to me that I need to make a subquery which inputs the hex value based on the unique cat_id. However, it has been many years since I worked with variable, so I'm struggling with the formatting of the statement.

My request

Does anyone have a suggestion how to build the conversion into the view?

Any assistance would be greatly appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MarcoC
  • 21
  • 3

1 Answers1

1

After searching for a few more hours, I stumbled onto the solution. Maybe it will help someone else in the future. The solution is remarkably simple, as is often the case.

My view, mentioned above, is called dbo.shelter_view

select sv.picture,sv.cat_id,
cast('' as xml).value(
    'xs:base64Binary(sql:column("sv.picture"))', 'varchar(max)'
) as Base64Encoding
from dbo.shelter_view as SV
MarcoC
  • 21
  • 3