Convert Image to Base64String in SELECT query

Question

I have a table my_table like this

CREATE TABLE my_table
    ([row_id] [int] IDENTITY(1,1) NOT NULL,
    [description] [varchar](50) NOT NULL,
    [img1] [image] NOT NULL)

I want to retrieve description and img1 as Base64String for a given row_id

CREATE PROCEDURE
    @row_id int
AS
    SELECT
        description,
        img1 -- Need to change this line
    FROM
        my_table
    WHERE
        row_id = @row_id

Searching online I found

SELECT CAST('' AS XML).value('xs:base64Binary(sql:variable("@bin"))','VARCHAR(MAX)')

which uses a variable @bin. How can I use this code in my select query and replace @bin with img1?

UPDATE

Here is what I'm doing for now

DECLARE @img1 varbinary(max)

SELECT @img1 = img1 FROM my_table WHERE row_id = @row_id

SELECT
    description,
    CAST('' AS XML).value('xs:base64Binary(sql:variable("@img1"))','VARCHAR(MAX)') AS img1
FROM
    my_table
WHERE
    row_id = @row_id

which is working but two parts are needed and I'm querying the same table twice. And as you might imagine this is a sample but I've got multiple columns I need to convert to base64.

`SELECT CAST('' AS XML).value('xs:base64Binary(sql:column("img1"))','VARCHAR(MAX)')` ? — gotqn, Jul 12 '16 at 09:26
@gotqn Thanks but I got `XQuery [value()]: The SQL type 'image' is not supported with sql:column() and sql:variable().` — Arthur Rey, Jul 12 '16 at 09:28
Do you really need to do this *in the server*? Is there really nothing *consuming* this result set capable of performing this conversion? (If you do need it in the server, a CLR function would probably work best, where you can just call `Convert.ToBase64String(...)`) — Damien_The_Unbeliever, Jul 12 '16 at 12:12
@Damien_The_Unbeliever I do need it server side indeed. Client side can't cope with `byte[]` for some reason. Never used CLR before, could you explain it further? — Arthur Rey, Jul 12 '16 at 12:33

score 4 · Accepted Answer · answered Dec 16 '20 at 06:42

This one might be a little bit better.

select 
    description,
    base64_img1 = CAST('' AS XML).value('xs:base64Binary(sql:column("img1"))','VARCHAR(MAX)')
from (
    select 
        description, 
        img1 = cast(img1 as varbinary(max))
    from my_table
    ) T

score 0 · Answer 2 · answered Jul 15 '16 at 14:45

You can use JSON functions to achieve base64 conversion, assuming your SQL Server version and database compatibility level are recent enough to support it:

select * from openjson((
    select description, img1
    from my_table
    where row_id = @row_id
    for json auto
)) with(description varchar(50), img1 varchar(max))

Convert Image to Base64String in SELECT query

2 Answers2