背景
SQL SERVER 2016 之前版本不支持 直接解析json
步骤:
1 vs 数据库项目
2 生成dll
3 加入信任
DECLARE @hash AS BINARY(64) = (SELECT HASHBYTES('SHA2_512', (SELECT * FROM OPENROWSET (BULK 'C:\Users\BianGX\source\repos\JsonSQLCLR\JsonSQLCLR\bin\Debug\JsonSQLCLR.dll', SINGLE_BLOB) AS [Data]))) EXEC sp_add_trusted_assembly @hash,N'JsonSQLCLR '
4 添加程序集
可以手动添加和删除
5 创建存储过程
CREATE PROCEDURE[dbo].[Json2Table] ( @jsonString NVARCHAR(max) ) WITH EXECUTE AS CALLER AS EXTERNAL NAME JsonSQLCLR.StoredProcedures.Json2Table --[SQL程序集名].[命名空间.类名].[方法名]
6
--1 查看 现有hash SELECT * FROM sys.trusted_assemblies GO -- 2 删除原来的信任hash DECLARE @hash AS BINARY(64); SET @hash = ( SELECT hash FROM sys.trusted_assemblies WHERE description = 'JsonSQLCLR' AND create_date = '2022-01-22 23:53:35.2809569' ); EXEC sys.sp_drop_trusted_assembly @hash; GO -- 添加新的 DECLARE @hash AS BINARY(64) = (SELECT HASHBYTES('SHA2_512', (SELECT * FROM OPENROWSET (BULK 'C:\Users\BianGX\source\repos\JsonSQLCLR\JsonSQLCLR\bin\Debug\JsonSQLCLR.dll', SINGLE_BLOB) AS [Data]))) EXEC sp_add_trusted_assembly @hash,N'JsonSQLCLR ' GO --创建存储过程 CREATE PROCEDURE[dbo].[Json2Table] ( @jsonString NVARCHAR(max) ) WITH EXECUTE AS CALLER AS EXTERNAL NAME JsonSQLCLR.StoredProcedures.Json2Table --[SQL程序集名].[命名空间.类名].[方法名]