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.

Answer 1

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