(SQL Server) Paraguay E-Invoicing Signed XML (SIFEN)

See more XML Digital Signatures Examples

Demonstrates how to sign XML for e-invoicing -- SISTEMA INTEGRADO DE FACTURACIÓN ELECTRÓNICA NACIONAL (SIFEN). This is for signing XML files that need to be submitted to a government agency (Country Paraguay, SIFEN / EKUATIA, Sistema Nacional de Facturacion Electrónica).

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 1

    -- Create the XML to be signed...

    -- Use this online tool to generate code from sample XML: 
    -- Generate Code to Create XML

    DECLARE @xmlToSign int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlToSign OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @xmlToSign, 'Tag', 'rDE'
    EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns', 'http://ekuatia.set.gov.py/sifen/xsd'
    EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance'
    EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xsi:schemaLocation', 'http://ekuatia.set.gov.py/sifen/xsd siRecepDE_v150.xsd'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'dVerFor', '150'
    EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'DE', 1, 'Id', '01800228774059001001501012022091519999999990'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|dDVId', '0'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|dFecFirma', '2022-09-15T10:23:05'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|dSisFact', '1'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gOpeDE|iTipEmi', '1'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gOpeDE|dDesTipEmi', 'Normal'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gOpeDE|dCodSeg', '999999999'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gOpeDE|dInfoEmi', 'GENERADA DE PEDIDO 131043202'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTimb|iTiDE', '1'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTimb|dDesTiDE', 'Factura electr�nica'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTimb|dNumTim', '18889014'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTimb|dEst', '059'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTimb|dPunExp', '001'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTimb|dNumDoc', '0015010'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTimb|dFeIniT', '2022-05-25'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|dFeEmiDE', '2022-09-15T10:22:57'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gOpeCom|iTipTra', '1'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gOpeCom|dDesTipTra', 'Venta de mercader�a'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gOpeCom|iTImp', '1'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gOpeCom|dDesTImp', 'IVA'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gOpeCom|cMoneOpe', 'PYG'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gOpeCom|dDesMoneOpe', 'Guarani'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|dRucEm', '80022877'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|dDVEmi', '4'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|iTipCont', '1'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|cTipReg', '1'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|dNomEmi', 'ABCDEF SA'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|dNomFanEmi', 'ABCDEF SA'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|dDirEmi', 'Jos� Abcde Xyz N� 666 e/Abxyz123'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|dNumCas', '0'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|dCompDir1', 'N/A'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|dCompDir2', 'N/A'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|cDepEmi', '12'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|dDesDepEmi', 'CENTRAL'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|cDisEmi', '153'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|dDesDisEmi', 'CAPIATA'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|cCiuEmi', '1234'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|dDesCiuEmi', 'CAPIATA'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|dTelEmi', '(022) 1111 000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|dEmailE', 'somebody@example.com.py'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|dDenSuc', 'PUNTO 99'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|gActEco|cActEco', '47721'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gEmis|gActEco|dDesActEco', 'COMERCIO AL POR MENOR DE PRODUCTOS'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gDatRec|iNatRec', '1'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gDatRec|iTiOpe', '2'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gDatRec|cPaisRec', 'PRY'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gDatRec|dDesPaisRe', 'Paraguay'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gDatRec|iTiContRec', '2'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gDatRec|dRucRec', '80082971'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gDatRec|dDVRec', '9'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gDatRec|dNomRec', 'XXXXXXX'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gDatRec|dNomFanRec', 'XXXXXXX'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gDatRec|dNumCasRec', '0'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDatGralOpe|gDatRec|dCodCliente', '9999999'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamFE|iIndPres', '1'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamFE|dDesIndPres', 'Operaci�n presencial'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamCond|iCondOpe', '1'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamCond|dDCondOpe', 'Contado'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamCond|gPaConEIni|iTiPago', '1'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamCond|gPaConEIni|dDesTiPag', 'Efectivo'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamCond|gPaConEIni|dMonTiPag', '100000.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamCond|gPaConEIni|cMoneTiPag', 'PYG'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamCond|gPaConEIni|dDMoneTiPag', 'Guarani'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamCond|gPaConEIni[1]|iTiPago', '1'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamCond|gPaConEIni[1]|dDesTiPag', 'Efectivo'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamCond|gPaConEIni[1]|dMonTiPag', '73600.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamCond|gPaConEIni[1]|cMoneTiPag', 'PYG'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamCond|gPaConEIni[1]|dDMoneTiPag', 'Guarani'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamItem|dCodInt', '818181'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamItem|dDesProSer', 'Widget Abc Xyz'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamItem|cUniMed', '77'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamItem|dDesUniMed', 'UNI'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamItem|dCantProSer', '2.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamItem|gValorItem|dPUniProSer', '16500.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamItem|gValorItem|dTotBruOpeItem', '33000.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamItem|gValorItem|gValorRestaItem|dDescItem', '3300.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamItem|gValorItem|gValorRestaItem|dPorcDesIt', '20.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamItem|gValorItem|gValorRestaItem|dTotOpeItem', '26400.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamItem|gCamIVA|iAfecIVA', '1'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamItem|gCamIVA|dDesAfecIVA', 'Gravado IVA'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamItem|gCamIVA|dPropIVA', '100'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamItem|gCamIVA|dTasaIVA', '5'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamItem|gCamIVA|dBasGravIVA', '25142.8571'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gDtipDE|gCamItem|gCamIVA|dLiqIVAItem', '1257.1429'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dSubExe', '0.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dSub5', '26400.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dSub10', '0.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dTotOpe', '26400.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dTotDesc', '6600.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dTotDescGlotem', '0.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dTotAntItem', '0.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dTotAnt', '0.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dPorcDescTotal', '0.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dDescTotal', '6600.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dAnticipo', '0.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dRedon', '0.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dTotGralOpe', '26400.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dIVA5', '1257.1429'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dIVA10', '0.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dTotIVA', '1257.1429'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dBaseGrav5', '25142.8571'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dBaseGrav10', '0.0000'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'DE|gTotSub|dTBasGraIVA', '25142.8571'

    DECLARE @gen int
    -- Use "Chilkat_9_5_0.XmlDSigGen" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.XmlDSigGen', @gen OUT

    EXEC sp_OASetProperty @gen, 'SigLocation', 'rDE'
    EXEC sp_OASetProperty @gen, 'SigLocationMod', 0
    EXEC sp_OASetProperty @gen, 'SigNamespacePrefix', ''
    EXEC sp_OASetProperty @gen, 'SigNamespaceUri', 'http://www.w3.org/2000/09/xmldsig#'
    EXEC sp_OASetProperty @gen, 'SignedInfoCanonAlg', 'C14N'
    EXEC sp_OASetProperty @gen, 'SignedInfoDigestMethod', 'sha256'

    -- -------- Reference 1 --------
    DECLARE @xml1 int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml1 OUT

    EXEC sp_OASetProperty @xml1, 'Tag', 'Transforms'
    EXEC sp_OAMethod @xml1, 'UpdateAttrAt', @success OUT, 'Transform', 1, 'Algorithm', 'http://www.w3.org/2000/09/xmldsig#enveloped-signature'
    EXEC sp_OAMethod @xml1, 'UpdateAttrAt', @success OUT, 'Transform[1]', 1, 'Algorithm', 'http://www.w3.org/2001/10/xml-exc-c14n#'

    EXEC sp_OAMethod @gen, 'AddSameDocRef2', @success OUT, '01800228774059001001501012022091516941989060', 'sha256', @xml1, ''

    -- Provide a certificate + private key. (PFX password is test123)
    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, 'qa_data/pfx/cert_test123.pfx', 'test123'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xmlToSign
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @xml1
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END
    EXEC sp_OAMethod @gen, 'SetX509Cert', @success OUT, @cert, 1

    EXEC sp_OASetProperty @gen, 'KeyInfoType', 'X509Data'
    EXEC sp_OASetProperty @gen, 'X509Type', 'Certificate'

    -- Load XML to be signed...
    DECLARE @sbXml int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT

    EXEC sp_OAMethod @xmlToSign, 'GetXmlSb', @success OUT, @sbXml

    EXEC sp_OASetProperty @gen, 'Behaviors', 'IndentedSignature'

    -- Sign the XML...
    EXEC sp_OAMethod @gen, 'CreateXmlDSigSb', @success OUT, @sbXml
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xmlToSign
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @xml1
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END
    -- -----------------------------------------------

    -- Save the signed XML to a file.
    EXEC sp_OAMethod @sbXml, 'WriteFile', @success OUT, 'c:/temp/qa_output/signedXml.xml', 'utf-8', 0

    EXEC sp_OAMethod @sbXml, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- ----------------------------------------
    -- Verify the signatures we just produced...
    DECLARE @verifier int
    -- Use "Chilkat_9_5_0.XmlDSig" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.XmlDSig', @verifier OUT

    EXEC sp_OAMethod @verifier, 'LoadSignatureSb', @success OUT, @sbXml
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @verifier, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xmlToSign
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @xml1
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @verifier
        RETURN
      END

    DECLARE @numSigs int
    EXEC sp_OAGetProperty @verifier, 'NumSignatures', @numSigs OUT
    DECLARE @verifyIdx int
    SELECT @verifyIdx = 0
    WHILE @verifyIdx < @numSigs
      BEGIN
        EXEC sp_OASetProperty @verifier, 'Selector', @verifyIdx
        DECLARE @verified int
        EXEC sp_OAMethod @verifier, 'VerifySignature', @verified OUT, 1
        IF @verified <> 1
          BEGIN
            EXEC sp_OAGetProperty @verifier, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @xmlToSign
            EXEC @hr = sp_OADestroy @gen
            EXEC @hr = sp_OADestroy @xml1
            EXEC @hr = sp_OADestroy @cert
            EXEC @hr = sp_OADestroy @sbXml
            EXEC @hr = sp_OADestroy @verifier
            RETURN
          END
        SELECT @verifyIdx = @verifyIdx + 1
      END

    PRINT 'All signatures were successfully verified.'

    EXEC @hr = sp_OADestroy @xmlToSign
    EXEC @hr = sp_OADestroy @gen
    EXEC @hr = sp_OADestroy @xml1
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @sbXml
    EXEC @hr = sp_OADestroy @verifier


END
GO