SQL Server
SQL Server
Apply 2nd Signature to sii.cl Factura Electrónica (Chile Servicio de Impuestos Internos)
See more XAdES Examples
Demonstrates how to apply the 2nd signature an XML invoice according to Chilean Internal Revenue Service regulations.Chilkat SQL Server Downloads
-- 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 = 0
-- 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.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 --------
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.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'
-- 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.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.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.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