在MSSQL中将varbinary值作为BASE64检索

Question

我正在寻找一种仅使用T-SQL从“__MigrationHistory”表中检索实体数据模型(EDM)的方法（因此，任何人都可以仅使用Microsoft SQL Server Management Studio执行相同操作）。 我想要一个有效的BASE64字符串值。 我不想完全解压缩它到EDMX。

我不想从*.resx迁移文件中获取它。

当我在SSMS中选择整个__MigrationHistory时，Model列的值如下所示。

0x1F8B0800000000000400CD57DB6EDB38107D5F60FF81E0D31648C5247DD906528BD4898BA075525469DF6969EC10CB8BCA4B607F5B1FFA49FB0B3BBACB521CB7E9A228020422357366E6F0CC50FEF7EBB7F8F54649720FD609A3137A121D53023A33B9D0EB8406BF7AFE377DFDEACF3FE2CB5C6DC8E7D6EE4569879EDA25F4CEFBE28C3197DD81E22E5222B3C699958F32A318CF0D3B3D3E7EC94E4E182004452C42E28F417BA1A05AE072667406850F5C2E4C0ED235FBF826AD50C93557E00A9E414253AE0A0917DCF325771035CB254278D8784ACEA5E098550A724509D7DA78EE31E7B34F0E526F8D5EA7056E7079BB2D00ED565C3A686A39EBCDBFB7ACE3D3B22CD63BB6505970DEA81F043C79D1F0C4C6EE4F629B763C229397C8B8DF9655576C26746E0C25E3386733694B9B433C47159C001721CC11D9353EEA84827A2AFF8EC82C481F2C241A82B75C1E910F612945F60EB6B7E61FD0890E520ED3C584F1DDCE066E7DB0A600EBB71F61D514719553C276FDD8D8B1731BF8D435BE0D029FAF31365F4AE8C4C01E752FFFB700A8286C144A167CF31EF4DADF25141F29998B0DE4ED4E83FA490BEC2B74F2368C82C4AC3F9DE999959473A1C136094C243F3944D47A738EAE09B75B518D9B82EFA5E028E973A8DB27AA14F2509E5D467DA3B2BA53DB8E667B5A3A5EF0A240CA062DDEEC90B4EEEFD9F3F4C7C5AE6A0C96B90734DF65DB45F2C6F2358CDE6268CC742EACF3AD902999E56A6236E67F0FB76DB401C5633DF78CB7C6E5F3CE29EFEBBFF26C46703D97732C4F81F655A5D06554F7FCC4AB1AB35C72FB409FCC8C0C4AEFEBB5C7BCEB3619FAD73B5384988DD21ED3C4263C8D3A7ECCF963921D9B74D13BE98E241A3772397C354DF4539B5082D4DC8BBCD2CED67950516910A55FE44C0AACB73758702D56E07C3D1429DE08A7A31BEDF7B95D9873B93C7CC5FCF2A11EB4F81200D9C46C5602EC4F0D787DCF6D76C7ED5F8A6F9E0D917E76884F67CDC111BD6F42D72A4B68BEC4C56D9D5E3DD19F38BBA7828FD9F08B2DBE0027D63D44F9FDA6212B95D483B636577A655A82B19C6146ADC988FF05789E232BE7160F90671E5F67E05C75D17EE632A0C9A55A427EA56F822F823F770ED4526E87F5C6ECF1F8D505B59B737C53942BF77F9480690A2C016EF49B2064DEE53D9FEA6F1F442990B780FB5587E28706C2ADB71DD2B5D1DF09D4D0770105E8B2296E016F120473373AE5F7F094DCF0FBE23DAC79B66DE7D67E90C307B14B7B7C21F8DA72E51A8CDEBFFC15C2CA9F21AFFE038574340FB80C0000

这是我的示例数据库，所以我不介意分享它。

该列的类型为varbinary(max)，我搜索了如何将其转换为类似UTF8的文本。

在SQL Server上将varbinary转换为字符串

SQL Server：将字符串转换为十六进制字符串并返回 #sql #sqlserver

对于

SELECT CONVERT(VARCHAR(max), 0x1F8B0800000000000400CD57DB6EDB3810..... , 0)

甚至更加直接

SELECT CONVERT(VARCHAR(max), (SELECT TOP (1) [t0].[Model]FROM [__MigrationHistory] AS [t0]), 0);

我得到了以下结果

字面意思： ‹

我在LinqPad5和使用这个工具找到的here中检查了我的Model，如果它没有损坏或不完整，但看起来还好。

- pizycki

Answer 1

终于找到了这篇文章：

https://social.technet.microsoft.com/wiki/contents/articles/36388.transact-sql-convert-varbinary-to-base64-string-and-vice-versa.aspx#Convert_VARBINARY_to_Base64_String

因此，运行查询可以得到我想要的有效Base64。

使用XML和提示“for xml path”

select Model, baze64
from __MigrationHistory
cross apply (select Model as '*' for xml path('')) T (baze64)

文章中提供的其他查询也可以工作。

使用XML XQuery

使用JSON

- pizycki

---

我注意到，“使用XML和提示“for xml path”比“使用XML XQuery”要快得多。” - Fabio Pagano

Answer 2

根据您的解决方案，我分享了两个标量函数的代码，可用于双向转换：

Base64Decode

CREATE FUNCTION [dbo].[fnBase64ToBinary]
(
    @Str AS NVARCHAR(MAX)
)
RETURNS VARBINARY(MAX)
AS
BEGIN
    RETURN (
        SELECT
                CONVERT(
                    VARBINARY(MAX), CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)')
                )
        FROM    (SELECT @Str AS BASE64_COLUMN) A
    );
END;

Base64编码

CREATE FUNCTION [dbo].[fnBinaryToBase64]
(
    @Var AS VARBINARY(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN (
        SELECT  @Var AS '*' FOR XML PATH('')
    );
END;

- Kilren

Answer 3

显然这是gzip压缩后的xml。例如：

SELECT MigrationId
      ,ContextKey
      ,cast(decompress(model) as xml) model
  FROM __MigrationHistory

- David Browne - Microsoft

---

这是一个更简单的解决方案。不过我想知道为什么在SMSS和LinqPad5中得到了不同的结果。有什么想法吗？ - pizycki

Answer 4

您可以使用XML和提示“for xml path”将二进制转换为Base64。

select Column1,ColumnWithBinary,ColumnToSwFinalResult
from TableWithBinary
cross apply (select ColumnWithBinaryas '*' for xml path('')) T (ColumnToSwFinalResult)
GO

- cain

Answer 5

死灵术。
您也可以像使用 XML PATH 一样使用 JSON PATH：

SELECT 
     T_AP_Dokumente.DK_UID 
    ,T_AP_Dokumente.DK_Thumbnail 
    ,tBase64.JSON_Thumbnail 
    ,NULLIF(T.XML_Thumbnail, '') AS base64  


    ,COMPRESS(T_AP_Dokumente.DK_Thumbnail) AS gzipped 
    ,DATALENGTH(COMPRESS(T_AP_Dokumente.DK_Thumbnail)) AS dlt_datalength_gzipped 
    ,DATALENGTH(T_AP_Dokumente.DK_Thumbnail) AS dlt_datalength 
    ,DATALENGTH(DECOMPRESS(COMPRESS(T_AP_Dokumente.DK_Thumbnail))) AS should_equal_dlt_datalength 
FROM T_AP_Dokumente 

CROSS APPLY 
(
    SELECT T_AP_Dokumente.DK_Thumbnail AS '*' FOR XML PATH('')
) AS T(XML_Thumbnail)

CROSS APPLY 
(
    SELECT * FROM 
    OPENJSON 
    (
        (
            SELECT T_AP_Dokumente.DK_Thumbnail AS JSON_Thumbnail 
            FOR JSON PATH 
        )
    ) WITH(JSON_Thumbnail varchar(MAX)) AS t 
) AS tBase64 

- Stefan Steiger