(SQL Server) Create sii.cl Factura Electrónica (Chile Servicio de Impuestos Internos)

Demonstrates how to sign an XML invoice according to Chilean Internal Revenue Service regulations.

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.

    -- See: Apply 2nd Signature to sii.cl Factura Electr�nica (Chile Servicio de Impuestos Internos)
    -- for an example showing how to apply a 2nd signature.

    -- We want to sign XML that looks like the following:

    -- <?xml version="1.0" encoding="iso-8859-1"?>
    -- <DTE version="1.0" xmlns="http://www.sii.cl/SiiDte">
    --     <Documento ID="F13T34">
    --         <Encabezado>
    --             <IdDoc>
    --                 <TipoDTE>34</TipoDTE>
    --                 <Folio>13</Folio>
    --                 <FchEmis>2020-07-17</FchEmis>
    --                 <FmaPago>1</FmaPago>
    --                 <FchVenc>2020-07-17</FchVenc>
    --             </IdDoc>
    --             <Emisor>
    --                 <RUTEmisor>...</RUTEmisor>
    --                 <RznSoc>...</RznSoc>
    --                 <GiroEmis>...</GiroEmis>
    --                 <Acteco>...</Acteco>
    --                 <DirOrigen>...</DirOrigen>
    --                 <CmnaOrigen>...</CmnaOrigen>
    --                 <CiudadOrigen>...</CiudadOrigen>
    --             </Emisor>
    --             <Receptor>
    --                 <RUTRecep>...</RUTRecep>
    --                 <RznSocRecep>...</RznSocRecep>
    --                 <GiroRecep>...</GiroRecep>
    --                 <Contacto/>
    --                 <DirRecep>...</DirRecep>
    --                 <CmnaRecep>...</CmnaRecep>
    --                 <CiudadRecep>...</CiudadRecep>
    --             </Receptor>
    --             <Totales>
    --                 <MntExe>14999</MntExe>
    --                 <MntTotal>14999</MntTotal>
    --             </Totales>
    --         </Encabezado>
    --         <Detalle>
    --             <NroLinDet>1</NroLinDet>
    --             <CdgItem>
    --                 <TpoCodigo>INT</TpoCodigo>
    --                 <VlrCodigo>1</VlrCodigo>
    --             </CdgItem>
    --             <NmbItem>Atenci�n profesional mes de Junio 2020</NmbItem>
    --             <QtyItem>1</QtyItem>
    --             <UnmdItem>UNI</UnmdItem>
    --             <PrcItem>14999</PrcItem>
    --             <MontoItem>14999</MontoItem>
    --         </Detalle>
    --         <TED version="1.0">
    --             <DD>
    --                 <RE>99972220-K</RE>
    --                 <TD>34</TD>
    --                 <F>13</F>
    --                 <FE>2020-07-17</FE>
    --                 <RR>99942999-2</RR>
    --                 <RSR>...</RSR>
    --                 <MNT>14999</MNT>
    --                 <IT1>Atencion profesional mes de Junio 2020</IT1>
    --                 <CAF version="1.0">
    --                     <DA>
    --                         <RE>99972220-K</RE>
    --                         <RS>...</RS>
    --                         <TD>34</TD>
    --                         <RNG>
    --                             <D>3</D>
    --                             <H>12</H>
    --                         </RNG>
    --                         <FA>2019-10-10</FA>
    --                         <RSAPK>
    --                             <M>2zHVYpcVNQRvS2yFuqdrh...TEQZx/m0t9HVTgWKZvlc6LSQ==</M>
    --                             <E>Aw==</E>
    --                         </RSAPK>
    --                         <IDK>300</IDK>
    --                     </DA>
    --                     <FRMA algoritmo="SHA1withRSA">LaVkjISGu...sBtsQL1jR9lw==</FRMA>
    --                 </CAF>
    --                 <TSTED>2020-07-17T13:19:10</TSTED>
    --             </DD>
    --             <FRMT algoritmo="SHA1withRSA">LxZr6zmXRZIfTz7...IXS6sp4vfz2fIsA==</FRMT>
    --         </TED>
    --         <TmstFirma>2020-07-17T13:19:10</TmstFirma>
    --     </Documento>
    -- </DTE>

    DECLARE @success int
    SELECT @success = 1

    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', 'DTE'
    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, 'F13T34', 'sha1', '', '', ''

    -- 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+KeyValue'
    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 @sbXml, 'LoadFile', @success OUT, 'qa_data/xml_dsig/sii_cl/xmlToSign.xml', 'iso-8859-1'
    IF @success = 0
      BEGIN

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

    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 @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_data/xml_dsig/sii_cl/signed1.xml', 'iso-8859-1', 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 @gen
        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 @gen
            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 @gen
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @sbXml
    EXEC @hr = sp_OADestroy @verifier


END
GO