Generate HASH of XML content in SQL

4

Is there a way to generate hash of a XML beyond the master.sys.fn_repl_hash_binary function?

Currently this is my implementation, but I do not know if it is the only way to do it.

--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'
    
asked by anonymous 20.05.2016 / 19:51

1 answer

1

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

See this example

    
13.01.2017 / 21:26