SQL Server
SQL Server
Create sii.cl Factura Electrónica (Chile Servicio de Impuestos Internos)
See more XAdES Examples
Demonstrates how to sign 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.
-- 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>
SELECT @success = 1
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', '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
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
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
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