(SQL Server) Sign XML for eFactura DGI Uruguay (cfe.dgi.gub.uy)

Demonstrates how to sign XML for eFactura DGI (Uruguay, cfe.dgi.gub.uy).

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)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @success int
    SELECT @success = 1

    -- <?xml version="1.0" encoding="utf-8"?>
    -- <DGICFE:EnvioCFE version="1.0" xsi:schemaLocation="http://cfe.dgi.gub.uy EnvioCFE_v1.30.xsd" xmlns:DGICFE="http://cfe.dgi.gub.uy" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    --     <DGICFE:Caratula version="1.0">
    --         <DGICFE:RutReceptor>...</DGICFE:RutReceptor>
    --         <DGICFE:RUCEmisor>...</DGICFE:RUCEmisor>
    --         <DGICFE:Idemisor>...</DGICFE:Idemisor>
    --         <DGICFE:CantCFE>1</DGICFE:CantCFE>
    --         <DGICFE:Fecha>2020-07-29T08:24:34-03:00</DGICFE:Fecha>
    --         <DGICFE:X509Certificate>MIIGc...UJwsTEnI
    -- </DGICFE:X509Certificate>
    --     </DGICFE:Caratula>
    --     <CFE xmlns="http://cfe.dgi.gub.uy" version="1.0">
    --         <eFact>
    --             <TmstFirma>2020-07-21T12:34:01-03:00</TmstFirma>
    --             <Encabezado>
    --                 <IdDoc>
    --                     <TipoCFE>111</TipoCFE>
    --                     <Serie>A</Serie>
    --                     <Nro>...</Nro>
    --                     <FchEmis>2020-07-21</FchEmis>
    --                     <FmaPago>2</FmaPago>
    --                     <FchVenc>2020-09-19</FchVenc>
    --                 </IdDoc>
    --                 <Emisor>
    --                     <RUCEmisor>...</RUCEmisor>
    --                     <RznSoc>...</RznSoc>
    --                     <NomComercial>...</NomComercial>
    --                     <GiroEmis>...</GiroEmis>
    --                     <Telefono>(598)...</Telefono>
    --                     <Telefono>(598)...</Telefono>
    --                     <CorreoEmisor>...</CorreoEmisor>
    --                     <EmiSucursal>...</EmiSucursal>
    --                     <CdgDGISucur>7</CdgDGISucur>
    --                     <DomFiscal>...</DomFiscal>
    --                     <Ciudad>Montevideo</Ciudad>
    --                     <Departamento>Montevideo</Departamento>
    --                 </Emisor>
    --                 <Receptor>
    --                     <TipoDocRecep>2</TipoDocRecep>
    --                     <CodPaisRecep>UY</CodPaisRecep>
    --                     <DocRecep>...</DocRecep>
    --                     <RznSocRecep>...</RznSocRecep>
    --                     <DirRecep>...</DirRecep>
    --                     <CiudadRecep>...</CiudadRecep>
    --                     <DeptoRecep>...</DeptoRecep>
    --                     <PaisRecep/>
    --                     <CP>...</CP>
    --                     <InfoAdicional/>
    --                     <LugarDestEnt>...</LugarDestEnt>
    --                     <CompraID>...</CompraID>
    --                 </Receptor>
    --                 <Totales>
    --                     <TpoMoneda>UYU</TpoMoneda>
    --                     <MntNoGrv>0.00</MntNoGrv>
    --                     <MntExpoyAsim>0.00</MntExpoyAsim>
    --                     <MntImpuestoPerc>0.00</MntImpuestoPerc>
    --                     <MntIVaenSusp>0.00</MntIVaenSusp>
    --                     <MntNetoIvaTasaMin>2701.71</MntNetoIvaTasaMin>
    --                     <MntNetoIVATasaBasica>0.00</MntNetoIVATasaBasica>
    --                     <MntNetoIVAOtra>0.00</MntNetoIVAOtra>
    --                     <IVATasaMin>10.00</IVATasaMin>
    --                     <IVATasaBasica>22.00</IVATasaBasica>
    --                     <MntIVATasaMin>270.17</MntIVATasaMin>
    --                     <MntIVATasaBasica>0.00</MntIVATasaBasica>
    --                     <MntIVAOtra>0.00</MntIVAOtra>
    --                     <MntTotal>2971.88</MntTotal>
    --                     <MntTotRetenido>0.00</MntTotRetenido>
    --                     <CantLinDet>1</CantLinDet>
    --                     <MontoNF>0.11</MontoNF>
    --                     <MntPagar>2971.99</MntPagar>
    --                 </Totales>
    --             </Encabezado>
    --             <Detalle>
    --                 <Item>
    --                     <NroLinDet>1</NroLinDet>
    --                     <IndFact>2</IndFact>
    --                     <NomItem>...</NomItem>
    --                     <DscItem>...</DscItem>
    --                     <Cantidad>4.000</Cantidad>
    --                     <UniMed/>
    --                     <PrecioUnitario>662.185000</PrecioUnitario>
    --                     <DescuentoPct>0.000</DescuentoPct>
    --                     <DescuentoMonto>0.00</DescuentoMonto>
    --                     <RecargoPct>2.000</RecargoPct>
    --                     <RecargoMnt>52.97</RecargoMnt>
    --                     <MontoItem>2701.71</MontoItem>
    --                 </Item>
    --             </Detalle>
    --             <CAEData>
    --                 <CAE_ID>90200658285</CAE_ID>
    --                 <DNro>1015000</DNro>
    --                 <HNro>1016999</HNro>
    --                 <FecVenc>2022-05-07</FecVenc>
    --             </CAEData>
    --         </eFact>
    --     </CFE>
    -- </DGICFE:EnvioCFE>

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

    EXEC sp_OASetProperty @gen, 'SigLocation', 'DGICFE:EnvioCFE|CFE'
    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', 'sha1'

    -- -------- Reference 1 --------
    EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, '', 'sha1', 'C14N', '', ''

    -- 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 @gen
        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', 'IssuerSerial'

    -- 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 @sbXml, 'LoadFile', @success OUT, 'qa_data/cfe.dgi.gub.uy/sample.xml', 'utf-8'
    IF @success = 0
      BEGIN

        PRINT 'Failed to load input XML document.'
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

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

    -- 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 @gen
        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, 'qa_output/signed.xml', 'utf-8', 0

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

    EXEC @hr = sp_OADestroy @gen
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @sbXml


END
GO