(SQL Server) Apply 2nd Signature to sii.cl Factura Electrónica (Chile Servicio de Impuestos Internos)

Demonstrates how to apply the 2nd signature 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
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- In this example: Create sii.cl Factura Electr�nica (Chile Servicio de Impuestos Internos)
    -- We signed XML structured like this:

    -- <?xml version="1.0" encoding="iso-8859-1"?>
    -- <DTE version="1.0" xmlns="http://www.sii.cl/SiiDte">
    --   <Documento ID="F11T22">
    --     ...
    --   </Documento>
    -- </DTE>

    -- To get a signature like this:
    -- <?xml version="1.0" encoding="iso-8859-1"?>
    -- <DTE version="1.0" xmlns="http://www.sii.cl/SiiDte">
    --   <Documento ID="F11T22">
    --     ...
    --   </Documento>
    --   <Signature xmlns="http://www.w3.org/2000/09/xmldsig#">
    --     ..
    --   </Signature>
    -- </DTE>

    -- Now we wish to wrap the result of the 1st example in the following and sign again:

    -- <?xml version="1.0" encoding="iso-8859-1"?>
    -- <EnvioDTE version="1.0" xmlns="http://www.sii.cl/SiiDte" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.sii.cl/SiiDte EnvioDTE_v10.xsd">
    --     <SetDTE ID="NDTECHILE">
    --         <Caratula version="1.0">
    --             <RutEmisor>...</RutEmisor>
    --             <RutEnvia>...</RutEnvia>
    --             <RutReceptor>...</RutReceptor>
    --             <FchResol>2014-08-22</FchResol>
    --             <NroResol>80</NroResol>
    --             <TmstFirmaEnv>2020-07-17T13:19:10</TmstFirmaEnv>
    --             <SubTotDTE>
    --                 <TpoDTE>34</TpoDTE>
    --                 <NroDTE>1</NroDTE>
    --             </SubTotDTE>
    --         </Caratula>
    --         <DTE version="1.0" xmlns="http://www.sii.cl/SiiDte">
    --             <Documento ID="F11T22">...</Documento>
    --             <Signature xmlns="http://www.w3.org/2000/09/xmldsig#">..</Signature>
    --         </DTE>
    --     </SetDTE>
    -- </EnvioDTE>

    -- The result will be this:

    -- <?xml version="1.0" encoding="iso-8859-1"?>
    -- <EnvioDTE version="1.0" xmlns="http://www.sii.cl/SiiDte" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.sii.cl/SiiDte EnvioDTE_v10.xsd">
    --     <SetDTE ID="NDTECHILE">
    --         <Caratula version="1.0">
    --             <RutEmisor>...</RutEmisor>
    --             <RutEnvia>...</RutEnvia>
    --             <RutReceptor>...</RutReceptor>
    --             <FchResol>2014-08-22</FchResol>
    --             <NroResol>80</NroResol>
    --             <TmstFirmaEnv>2020-07-17T13:19:10</TmstFirmaEnv>
    --             <SubTotDTE>
    --                 <TpoDTE>34</TpoDTE>
    --                 <NroDTE>1</NroDTE>
    --             </SubTotDTE>
    --         </Caratula>
    --         <DTE version="1.0" xmlns="http://www.sii.cl/SiiDte">
    --             <Documento ID="F11T22">...</Documento>
    --             <Signature xmlns="http://www.w3.org/2000/09/xmldsig#">..</Signature>
    --         </DTE>
    --     </SetDTE>
    --     <Signature xmlns="http://www.w3.org/2000/09/xmldsig#">..</Signature>
    -- </EnvioDTE>

    DECLARE @gen int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.XmlDSigGen', @gen OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @gen, 'SigLocation', 'EnvioDTE'
    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 --------
    DECLARE @success int
    EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'NDTECHILE', 'sha1', '', '', ''

    -- Provide a certificate + private key. (PFX password is test123)
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Cert', @cert OUT

    DECLARE @success int
    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'

    -- Here's the part where we must be very careful not to disturb the existing signature.
    -- We want to encapsulate the existing signed XML like this:

    -- <?xml version="1.0" encoding="iso-8859-1"?>
    -- <EnvioDTE version="1.0" xmlns="http://www.sii.cl/SiiDte" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.sii.cl/SiiDte EnvioDTE_v10.xsd">
    --     <SetDTE ID="NDTECHILE">
    --         <Caratula version="1.0">
    --             <RutEmisor>...</RutEmisor>
    --             <RutEnvia>...</RutEnvia>
    --             <RutReceptor>...</RutReceptor>
    --             <FchResol>2014-08-22</FchResol>
    --             <NroResol>80</NroResol>
    --             <TmstFirmaEnv>2020-07-17T13:19:10</TmstFirmaEnv>
    --             <SubTotDTE>
    --                 <TpoDTE>34</TpoDTE>
    --                 <NroDTE>1</NroDTE>
    --             </SubTotDTE>
    --         </Caratula>
    --         <EXISTING_SIGNED_XML>HERE</EXISTING_SIGNED_XML>
    --     </SetDTE>
    --     <Signature xmlns="http://www.w3.org/2000/09/xmldsig#">..</Signature>
    -- </EnvioDTE>

    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Xml', @xml OUT

    EXEC sp_OASetProperty @xml, 'Tag', 'EnvioDTE'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'version', '1.0'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns', 'http://www.sii.cl/SiiDte'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns:xsi', 'http://www.w3.org/2001/XMLSchema-instance'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xsi:schemaLocation', 'http://www.sii.cl/SiiDte EnvioDTE_v10.xsd'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'SetDTE', 1, 'ID', 'NDTECHILE'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'SetDTE|Caratula', 1, 'version', '1.0'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'SetDTE|Caratula|RutEmisor', '...'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'SetDTE|Caratula|RutEnvia', '...'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'SetDTE|Caratula|RutReceptor', '...'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'SetDTE|Caratula|FchResol', '2014-08-22'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'SetDTE|Caratula|NroResol', '80'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'SetDTE|Caratula|TmstFirmaEnv', '2020-07-17T13:19:10'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'SetDTE|Caratula|SubTotDTE|TpoDTE', '34'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'SetDTE|Caratula|SubTotDTE|NroDTE', '1'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'SetDTE|EXISTING_SIGNED_XML', 'HERE'

    DECLARE @sbXml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbXml OUT

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

    -- Load our existing signed XML.
    -- We cannot load it into a Chilkat XML object because we cannot allow anything to change,
    -- not even indentation or whitespace.
    DECLARE @sbExisting int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbExisting OUT

    EXEC sp_OAMethod @sbExisting, 'LoadFile', @success OUT, 'qa_data/xml_dsig/sii_cl/signed1.xml', 'iso-8859-1'

    -- However, we must remove the XML declarator, and we can trim the whitespace at the very front and back (i.e. whitespace completely outside the document).
    DECLARE @numReplaced int
    EXEC sp_OAMethod @sbExisting, 'Replace', @numReplaced OUT, '<?xml version="1.0" encoding="iso-8859-1"?>', ''
    EXEC sp_OAMethod @sbExisting, 'Trim', @success OUT

    -- Insert the existing signed XML into the new XML document to be signed.
    EXEC sp_OAMethod @sbExisting, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @sbXml, 'Replace', @numReplaced OUT, '<EXISTING_SIGNED_XML>HERE</EXISTING_SIGNED_XML>', @sTmp0

    -- We now have the XML document ready for the 2nd signature to be applied...

    -- Make sure we add the "SignExistingSignatures" behavior so that this signature includes the original signature in the digest.
    EXEC sp_OASetProperty @gen, 'Behaviors', 'IndentedSignature,SignExistingSignatures'

    -- 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 @xml
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @sbExisting
        RETURN
      END
    -- -----------------------------------------------

    -- Change the XML declarator to indicate iso-8859-1 (because we'll be saving using iso-8859-1)
    EXEC sp_OAMethod @sbXml, 'Replace', @numReplaced OUT, 'encoding="utf-8"?>', 'encoding="iso-8859-1"?>'

    -- Save the signed XML to a file.
    EXEC sp_OAMethod @sbXml, 'WriteFile', @success OUT, 'qa_data/xml_dsig/sii_cl/signed2.xml', 'iso-8859-1', 0

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

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


END
GO