I'm looking for a way for retrieving Entity Data Model (EDM) from __MigrationHistory
table using only T-SQL (so anyone, using Microsoft SQL Server Management Studio only, could do the same).
I want to have a valid BASE64 string value.
I don't want to fully decompress it to EDMX.
I don't want to get it from *.resx
migration file.
When I select whole __MigrationHistory
in SSMS, Model
column value is like following
0x1F8B0800000000000400CD57DB6EDB38107D5F60FF81E0D31648C5247DD906528BD4898BA075525469DF6969EC10CB8BCA4B607F5B1FFA49FB0B3BBACB521CB7E9A228020422357366E6F0CC50FEF7EBB7F8F54649720FD609A3137A121D53023A33B9D0EB8406BF7AFE377DFDEACF3FE2CB5C6DC8E7D6EE4569879EDA25F4CEFBE28C3197DD81E22E5222B3C699958F32A318CF0D3B3D3E7EC94E4E182004452C42E28F417BA1A05AE072667406850F5C2E4C0ED235FBF826AD50C93557E00A9E414253AE0A0917DCF325771035CB254278D8784ACEA5E098550A724509D7DA78EE31E7B34F0E526F8D5EA7056E7079BB2D00ED565C3A686A39EBCDBFB7ACE3D3B22CD63BB6505970DEA81F043C79D1F0C4C6EE4F629B763C229397C8B8DF9655576C26746E0C25E3386733694B9B433C47159C001721CC11D9353EEA84827A2AFF8EC82C481F2C241A82B75C1E910F612945F60EB6B7E61FD0890E520ED3C584F1DDCE066E7DB0A600EBB71F61D514719553C276FDD8D8B1731BF8D435BE0D029FAF31365F4AE8C4C01E752FFFB700A8286C144A167CF31EF4DADF25141F29998B0DE4ED4E83FA490BEC2B74F2368C82C4AC3F9DE999959473A1C136094C243F3944D47A738EAE09B75B518D9B82EFA5E028E973A8DB27AA14F2509E5D467DA3B2BA53DB8E667B5A3A5EF0A240CA062DDEEC90B4EEEFD9F3F4C7C5AE6A0C96B90734DF65DB45F2C6F2358CDE6268CC742EACF3AD902999E56A6236E67F0FB76DB401C5633DF78CB7C6E5F3CE29EFEBBFF26C46703D97732C4F81F655A5D06554F7FCC4AB1AB35C72FB409FCC8C0C4AEFEBB5C7BCEB3619FAD73B5384988DD21ED3C4263C8D3A7ECCF963921D9B74D13BE98E241A3772397C354DF4539B5082D4DC8BBCD2CED67950516910A55FE44C0AACB73758702D56E07C3D1429DE08A7A31BEDF7B95D9873B93C7CC5FCF2A11EB4F81200D9C46C5602EC4F0D787DCF6D76C7ED5F8A6F9E0D917E76884F67CDC111BD6F42D72A4B68BEC4C56D9D5E3DD19F38BBA7828FD9F08B2DBE0027D63D44F9FDA6212B95D483B636577A655A82B19C6146ADC988FF05789E232BE7160F90671E5F67E05C75D17EE632A0C9A55A427EA56F822F823F770ED4526E87F5C6ECF1F8D505B59B737C53942BF77F9480690A2C016EF49B2064DEE53D9FEA6F1F442990B780FB5587E28706C2ADB71DD2B5D1DF09D4D0770105E8B2296E016F120473373AE5F7F094DCF0FBE23DAC79B66DE7D67E90C307B14B7B7C21F8DA72E51A8CDEBFFC15C2CA9F21AFFE038574340FB80C0000
This is my sample database, so I don't mind sharing it.
The column is of type varbinary(max)
, I googled how can convert it to UTF8*-ish* text.
varbinary to string on SQL Server
SQL Server: Convert a string into a hex string and back #sql #sqlserver
For
SELECT CONVERT(VARCHAR(max), 0x1F8B0800000000000400CD57DB6EDB3810..... , 0)
or even more straightforward
SELECT CONVERT(VARCHAR(max), (SELECT TOP (1) [t0].[Model]FROM [__MigrationHistory] AS [t0]), 0);
I get the following result
literally this: ‹
I checked my Model
if it's not broken or incomplete in both LinqPad5 and using this tool found here, but it looks ok.
INSERT INTO #TempTable(PK, VarBinaryColumn) SELECT PK, VarBinaryColumn FROM dbo. YourPermanentTable; If you need to convert the varbinary data back to the original file text format in T-SQL, you can use CAST or CONVERT to convert to varchar or nvarchar as long as the data was originally ASCII or Unicode.
Description. The VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than non-binary character strings. M represents the maximum column length in bytes. It contains no character set, and comparison and sorting are based on the numeric value of the bytes.
value( 'xs:base64Binary(xs:hexBinary(sql:column("bin")))' , 'VARCHAR(MAX)' ) Base64Encoding FROM ( SELECT CAST('TestData' AS VARBINARY(MAX)) AS bin ) AS bin_sql_server_temp; -- Decode the Base64-encoded string "VGVzdERhdGE=" to get back "TestData" SELECT CAST( CAST(N'' AS XML).
Finally found this article:
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
So, running the query gets what I wanted, valid Base64.
Using XML and the hint "for xml path"
select Model, baze64
from __MigrationHistory
cross apply (select Model as '*' for xml path('')) T (baze64)
Other presented queries in article will also work
Using XML XQuery
Using JSON
It's apparently gzip'd xml. eg
SELECT MigrationId
,ContextKey
,cast(decompress(model) as xml) model
FROM __MigrationHistory
Based on your solution I'm sharing the code for 2 scalar function for conversion in both direction:
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 encode
CREATE FUNCTION [dbo].[fnBinaryToBase64]
(
@Var AS VARBINARY(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN (
SELECT @Var AS '*' FOR XML PATH('')
);
END;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With