在SELECT查询中将图像转换为Base64字符串

Question

我有一个名为my_table的表格，如下所示：

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

我想要检索给定row_id的description和img1作为Base64字符串。

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

在网上搜索我发现

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

此处使用了变量@bin。我如何在我的选取查询中使用此代码并将@bin替换为img1？

更新

以下是我现在正在做的

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

这段代码可以工作，但需要两个部分，并且我正在查询相同的表两次。您可能想象得到，这只是一个示例，我有多个需要转换为base64的列。

- Arthur Rey

---

SELECT CAST('' AS XML).value('xs:base64Binary(sql:column("img1"))','VARCHAR(MAX)') 选择 CAST('' AS XML).value('xs:base64Binary(sql:column("img1"))','VARCHAR(MAX)') - gotqn

@gotqn 谢谢，但我得到了 XQuery [value()]: SQL 类型“image”不支持 sql:column() 和 sql:variable()。 - Arthur Rey

你真的需要在服务器端执行这个操作吗？难道没有任何能够执行这种转换并消耗结果集的东西吗？（如果确实需要在服务器端执行，CLR函数可能是最好的选择，你可以直接调用 Convert.ToBase64String(...)） - Damien_The_Unbeliever

@Damien_The_Unbeliever，我确实需要在服务器端处理它。由于某些原因，客户端无法处理 byte[]。我以前从未使用过CLR，请您进一步解释一下吗？ - Arthur Rey

Answer 1

这个可能会更好一些。

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

- Steven He

Answer 2

您可以使用JSON函数实现Base64转换，假设您的SQL Server版本和数据库兼容性水平足够新来支持它：

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

- Sergei Patiakin