4
Is there a way to generate the hash
of a XML
beyond the function master.sys.fn_repl_hash_binary
?
Currently this is my implementation, however, I do not know if it is the only way to do.
--Preparação do ambiente
CREATE TABLE [dbo].[Funcionarios]([FuncionarioId] [int] NOT NULL, [Nome] [varchar](50) NULL)
INSERT INTO [dbo].[Funcionarios] (FuncionarioId, Nome) values(1,'jose')
INSERT INTO [dbo].[Funcionarios] (FuncionarioId, Nome) values(2,'maria')
INSERT INTO [dbo].[Funcionarios] (FuncionarioId, Nome) values(3,'joão')
INSERT INTO [dbo].[Funcionarios] (FuncionarioId, Nome) values(4,'pedro')
DECLARE @XML XML
DECLARE @HASH CHAR(32)
--Esse é um select de exemplo
SELECT @XML = (select * FROM Funcionarios FOR XML PATH('FUNC'))
--Gero o hash com essa função
SET @HASH = CONVERT(VARCHAR(MAX), master.sys.fn_repl_hash_binary(CAST((SELECT @XML).value('.','VARCHAR(max)') AS VARBINARY(MAX))), 2);
--Crio um outro xml incluindo o hash na tag epílogo
WITH
XMLNAMESPACES('http://www.w3.org/2001/XMLSchema' AS xsd,
'http://www.w3.org/2001/XMLSchema-instance' AS xsi)
SELECT @XML = (SELECT
(SELECT @XML
, (SELECT @HASH 'xml:hash'
FOR XML PATH ('xml:epilogo'), ELEMENTS, TYPE)
FOR XML PATH('xml:teste'),ELEMENTS,TYPE)
FOR XML PATH(''), TYPE)
--Resultado final
SELECT @XML 'XML'
In
CLR
c#
I already did. As an alternative solution, I built inSQL
not to have to register theassembly
client server. As my application grew, I formalized aCLR
. The question will still remain to find another solution inSQL
.– Ismael