Generate XML content HASH in SQL

Asked

Viewed 145 times

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'

1 answer

1

In that case what you can do is create a CLR and hash via CLR that in the case would be in C#.

Take this example

  • In CLR c# I already did. As an alternative solution, I built in SQL not to have to register the assembly client server. As my application grew, I formalized a CLR. The question will still remain to find another solution in SQL.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.