SQL Server
SQL Server
Cape Verde Fatura Eletrónica XML Digital Signature
See more XML Digital Signatures Examples
Demonstrates how to sign XML to create a signed invoice compatible with the Cape Verde Fatura Eletrónica requirements.Note: This example requires Chilkat v9.5.0.89 or greater for a small feature to provide the ability produce mult-line base64 signature ending with
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.
-- First create the following XML to be signed. This can be created programmatically as shown in the code below, or
-- you can simply load the XML to be signed from a file..
-- <?xml version="1.0" encoding="utf-8"?>
-- <Dfe xmlns="urn:cv:efatura:xsd:v1.0" DocumentTypeCode="1" Id="CV2210818123456789000010100000000546735014601" Version="1.0">
-- <Invoice>
-- <LedCode>1</LedCode>
-- <Serie>SL</Serie>
-- <DocumentNumber>5</DocumentNumber>
-- <IssueDate>2021-08-30</IssueDate>
-- <IssueTime>00:01:00</IssueTime>
-- <TaxPointDate>2021-08-18</TaxPointDate>
-- <EmitterParty>
-- <TaxId CountryCode="CV">123456789</TaxId>
-- <Name>Maria</Name>
-- <Address CountryCode="CV">
-- <State>Estado do Emissor</State>
-- <City>Cidade do Emissor</City>
-- <Region>Regi�o do Emissor</Region>
-- <Street>Rua do Emissor</Street>
-- <StreetDetail>Detalhe da rua do Emissor</StreetDetail>
-- <BuildingName>Pr�dio do Emissor</BuildingName>
-- <BuildingNumber>1</BuildingNumber>
-- <BuildingFloor>2</BuildingFloor>
-- <PostalCode>7600</PostalCode>
-- <AddressDetail>Detalhe de endere�o do Emissor</AddressDetail>
-- <AddressCode>CV774999999999999319</AddressCode>
-- </Address>
-- <Contacts>
-- <Telephone>1234567</Telephone>
-- <Mobilephone>1234568</Mobilephone>
-- <Telefax>1234569</Telefax>
-- <Email>somebody@example.com</Email>
-- <Website>emissor.efatura.cv</Website>
-- </Contacts>
-- </EmitterParty>
-- <ReceiverParty>
-- <TaxId CountryCode="CV">123456789</TaxId>
-- <Name>Nome do Recetor</Name>
-- <Address CountryCode="CV">
-- <State>Estado do Recetor</State>
-- <City>Cidade do Recetor</City>
-- <Region>Regi�o do Recetor</Region>
-- <Street>Rua do Recetor</Street>
-- <StreetDetail>Detalhe da rua do Recetor</StreetDetail>
-- <BuildingName>Pr�dio do Recetor</BuildingName>
-- <BuildingNumber>2</BuildingNumber>
-- <BuildingFloor>3</BuildingFloor>
-- <PostalCode>7601</PostalCode>
-- <AddressDetail>Detalhe de endere�o do Recetor</AddressDetail>
-- <AddressCode>CV774999999999999319</AddressCode>
-- </Address>
-- <Contacts>
-- <Telephone>2234567</Telephone>
-- <Mobilephone>2234568</Mobilephone>
-- <Telefax>2234569</Telefax>
-- <Email>rector@efatura.cv</Email>
-- <Website>rector.efatura.cv</Website>
-- </Contacts>
-- </ReceiverParty>
-- <Lines>
-- <Line LineTypeCode="N">
-- <Id>01</Id>
-- <OrderLineReference>1</OrderLineReference>
-- <Quantity UnitCode="EA">3.15</Quantity>
-- <Price>123456</Price>
-- <PriceExtension>388886</PriceExtension>
-- <Discount>15</Discount>
-- <NetTotal>252776</NetTotal>
-- <Tax TaxTypeCode="IVA">
-- <TaxPercentage>15</TaxPercentage>
-- </Tax>
-- <Item>
-- <Description>Telem�vel</Description>
-- <Name>Telem�vel</Name>
-- <BrandName>Apple</BrandName>
-- <ModelName>iPhone X</ModelName>
-- <EmitterIdentification>TLMV</EmitterIdentification>
-- <StandardIdentification>
-- <GTIN>12345</GTIN>
-- </StandardIdentification>
-- <HazardousRiskIndicator>false</HazardousRiskIndicator>
-- </Item>
-- </Line>
-- </Lines>
-- <Totals>
-- <PriceExtensionTotalAmount>388886</PriceExtensionTotalAmount>
-- <ChargeTotalAmount>0</ChargeTotalAmount>
-- <DiscountTotalAmount>136110</DiscountTotalAmount>
-- <NetTotalAmount>252776</NetTotalAmount>
-- <Discount ValueType="P">20</Discount>
-- <TaxTotalAmount>37916</TaxTotalAmount>
-- <PayableAmount>290692</PayableAmount>
-- </Totals>
-- <References>
-- <Reference>
-- <FiscalDocument IsOldDocument="false">CV1200520123456789000112345678901112345678901</FiscalDocument>
-- <PaymentAmount>12345</PaymentAmount>
-- <Tax TaxTypeCode="IR">
-- <TaxPercentage>10</TaxPercentage>
-- </Tax>
-- </Reference>
-- <Reference>
-- <FiscalDocument IsOldDocument="false">CV1200520123456789000112345678901112345678903</FiscalDocument>
-- <PaymentAmount>98765</PaymentAmount>
-- <Tax TaxTypeCode="IVA">
-- <TaxPercentage>15</TaxPercentage>
-- </Tax>
-- </Reference>
-- </References>
-- <Payments>
-- <PaymentDueDate>2021-12-31</PaymentDueDate>
-- <PaymentTerms>
-- <Note>Juros de 12% a partir da data de vencimento</Note>
-- </PaymentTerms>
-- <PayeeFinancialAccount>
-- <NIB>123456789012345678901</NIB>
-- <Name>Nome da Conta 1</Name>
-- </PayeeFinancialAccount>
-- <PayeeFinancialAccount>
-- <NIB>123456789012345678902</NIB>
-- <Name>Nome da Conta 2</Name>
-- </PayeeFinancialAccount>
-- </Payments>
-- <Note>Texto livre</Note>
-- </Invoice>
-- <Transmission>
-- <IssueMode>2</IssueMode>
-- <TransmitterTaxId CountryCode="CV">123456789</TransmitterTaxId>
-- <Software>
-- <Code>EMIPUB</Code>
-- <Name>DNRE Emissor P�blico</Name>
-- <Version>Build #53 12-07-2021 14:00:47 CVT - master</Version>
-- </Software>
-- <Contingency>
-- <LedCode>1</LedCode>
-- <IssueDate>2020-01-01</IssueDate>
-- <IssueTime>01:02:03</IssueTime>
-- <ReasonTypeCode>1</ReasonTypeCode>
-- </Contingency>
-- </Transmission>
-- <RepositoryCode>2</RepositoryCode>
-- </Dfe>
SELECT @success = 1
-- Create the XML to be signed...
-- Use this online tool to generate code from sample XML:
-- Generate Code to Create XML
DECLARE @xmlToSign int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlToSign OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @xmlToSign, 'Tag', 'Dfe'
EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns', 'urn:cv:efatura:xsd:v1.0'
EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'DocumentTypeCode', '1'
EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'Id', 'CV2210818123456789000010100000000546735014601'
EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'Version', '1.0'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|LedCode', '1'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Serie', 'SL'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|DocumentNumber', '5'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|IssueDate', '2021-08-30'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|IssueTime', '00:01:00'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|TaxPointDate', '2021-08-18'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'Invoice|EmitterParty|TaxId', 1, 'CountryCode', 'CV'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|EmitterParty|TaxId', '123456789'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|EmitterParty|Name', 'Maria'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'Invoice|EmitterParty|Address', 1, 'CountryCode', 'CV'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|EmitterParty|Address|State', 'Estado do Emissor'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|EmitterParty|Address|City', 'Cidade do Emissor'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|EmitterParty|Address|Region', 'Regi�o do Emissor'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|EmitterParty|Address|Street', 'Rua do Emissor'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|EmitterParty|Address|StreetDetail', 'Detalhe da rua do Emissor'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|EmitterParty|Address|BuildingName', 'Pr�dio do Emissor'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|EmitterParty|Address|BuildingNumber', '1'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|EmitterParty|Address|BuildingFloor', '2'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|EmitterParty|Address|PostalCode', '7600'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|EmitterParty|Address|AddressDetail', 'Detalhe de endere�o do Emissor'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|EmitterParty|Address|AddressCode', 'CV774999999999999319'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|EmitterParty|Contacts|Telephone', '1234567'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|EmitterParty|Contacts|Mobilephone', '1234568'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|EmitterParty|Contacts|Telefax', '1234569'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|EmitterParty|Contacts|Email', 'somebody@example.com'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|EmitterParty|Contacts|Website', 'emissor.efatura.cv'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'Invoice|ReceiverParty|TaxId', 1, 'CountryCode', 'CV'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|ReceiverParty|TaxId', '123456789'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|ReceiverParty|Name', 'Nome do Recetor'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'Invoice|ReceiverParty|Address', 1, 'CountryCode', 'CV'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|ReceiverParty|Address|State', 'Estado do Recetor'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|ReceiverParty|Address|City', 'Cidade do Recetor'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|ReceiverParty|Address|Region', 'Regi�o do Recetor'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|ReceiverParty|Address|Street', 'Rua do Recetor'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|ReceiverParty|Address|StreetDetail', 'Detalhe da rua do Recetor'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|ReceiverParty|Address|BuildingName', 'Pr�dio do Recetor'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|ReceiverParty|Address|BuildingNumber', '2'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|ReceiverParty|Address|BuildingFloor', '3'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|ReceiverParty|Address|PostalCode', '7601'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|ReceiverParty|Address|AddressDetail', 'Detalhe de endere�o do Recetor'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|ReceiverParty|Address|AddressCode', 'CV774999999999999319'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|ReceiverParty|Contacts|Telephone', '2234567'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|ReceiverParty|Contacts|Mobilephone', '2234568'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|ReceiverParty|Contacts|Telefax', '2234569'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|ReceiverParty|Contacts|Email', 'rector@efatura.cv'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|ReceiverParty|Contacts|Website', 'rector.efatura.cv'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'Invoice|Lines|Line', 1, 'LineTypeCode', 'N'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Lines|Line|Id', '01'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Lines|Line|OrderLineReference', '1'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'Invoice|Lines|Line|Quantity', 1, 'UnitCode', 'EA'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Lines|Line|Quantity', '3.15'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Lines|Line|Price', '123456'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Lines|Line|PriceExtension', '388886'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Lines|Line|Discount', '15'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Lines|Line|NetTotal', '252776'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'Invoice|Lines|Line|Tax', 1, 'TaxTypeCode', 'IVA'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Lines|Line|Tax|TaxPercentage', '15'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Lines|Line|Item|Description', 'Telem�vel'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Lines|Line|Item|Name', 'Telem�vel'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Lines|Line|Item|BrandName', 'Apple'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Lines|Line|Item|ModelName', 'iPhone X'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Lines|Line|Item|EmitterIdentification', 'TLMV'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Lines|Line|Item|StandardIdentification|GTIN', '12345'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Lines|Line|Item|HazardousRiskIndicator', 'false'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Totals|PriceExtensionTotalAmount', '388886'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Totals|ChargeTotalAmount', '0'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Totals|DiscountTotalAmount', '136110'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Totals|NetTotalAmount', '252776'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'Invoice|Totals|Discount', 1, 'ValueType', 'P'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Totals|Discount', '20'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Totals|TaxTotalAmount', '37916'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Totals|PayableAmount', '290692'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'Invoice|References|Reference|FiscalDocument', 1, 'IsOldDocument', 'false'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|References|Reference|FiscalDocument', 'CV1200520123456789000112345678901112345678901'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|References|Reference|PaymentAmount', '12345'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'Invoice|References|Reference|Tax', 1, 'TaxTypeCode', 'IR'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|References|Reference|Tax|TaxPercentage', '10'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'Invoice|References|Reference[1]|FiscalDocument', 1, 'IsOldDocument', 'false'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|References|Reference[1]|FiscalDocument', 'CV1200520123456789000112345678901112345678903'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|References|Reference[1]|PaymentAmount', '98765'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'Invoice|References|Reference[1]|Tax', 1, 'TaxTypeCode', 'IVA'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|References|Reference[1]|Tax|TaxPercentage', '15'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Payments|PaymentDueDate', '2021-12-31'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Payments|PaymentTerms|Note', 'Juros de 12% a partir da data de vencimento'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Payments|PayeeFinancialAccount|NIB', '123456789012345678901'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Payments|PayeeFinancialAccount|Name', 'Nome da Conta 1'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Payments|PayeeFinancialAccount[1]|NIB', '123456789012345678902'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Payments|PayeeFinancialAccount[1]|Name', 'Nome da Conta 2'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Invoice|Note', 'Texto livre'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Transmission|IssueMode', '2'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'Transmission|TransmitterTaxId', 1, 'CountryCode', 'CV'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Transmission|TransmitterTaxId', '123456789'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Transmission|Software|Code', 'EMIPUB'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Transmission|Software|Name', 'DNRE Emissor P�blico'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Transmission|Software|Version', 'Build #53 12-07-2021 14:00:47 CVT - master'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Transmission|Contingency|LedCode', '1'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Transmission|Contingency|IssueDate', '2020-01-01'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Transmission|Contingency|IssueTime', '01:02:03'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'Transmission|Contingency|ReasonTypeCode', '1'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'RepositoryCode', '2'
DECLARE @gen int
EXEC @hr = sp_OACreate 'Chilkat.XmlDSigGen', @gen OUT
EXEC sp_OASetProperty @gen, 'SigLocation', 'Dfe'
EXEC sp_OASetProperty @gen, 'SigLocationMod', 0
EXEC sp_OASetProperty @gen, 'SigId', 'xmldsig-cbd86598-e75e-4ae4-9a27-3b4ee2a789ac'
EXEC sp_OASetProperty @gen, 'SigNamespacePrefix', 'ds'
EXEC sp_OASetProperty @gen, 'SigNamespaceUri', 'http://www.w3.org/2000/09/xmldsig#'
EXEC sp_OASetProperty @gen, 'SigValueId', 'xmldsig-cbd86598-e75e-4ae4-9a27-3b4ee2a789ac-sigvalue'
EXEC sp_OASetProperty @gen, 'SignedInfoCanonAlg', 'C14N'
EXEC sp_OASetProperty @gen, 'SignedInfoDigestMethod', 'sha256'
-- Set the KeyInfoId before adding references..
EXEC sp_OASetProperty @gen, 'KeyInfoId', 'xmldsig-cbd86598-e75e-4ae4-9a27-3b4ee2a789ac-keyinfo'
-- Create an Object to be added to the Signature.
DECLARE @object1 int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @object1 OUT
EXEC sp_OASetProperty @object1, 'Tag', 'xades:QualifyingProperties'
EXEC sp_OAMethod @object1, 'AddAttribute', @success OUT, 'xmlns:xades', 'http://uri.etsi.org/01903/v1.3.2#'
EXEC sp_OAMethod @object1, 'AddAttribute', @success OUT, 'xmlns:xades141', 'http://uri.etsi.org/01903/v1.4.1#'
EXEC sp_OAMethod @object1, 'AddAttribute', @success OUT, 'Target', '#xmldsig-cbd86598-e75e-4ae4-9a27-3b4ee2a789ac'
EXEC sp_OAMethod @object1, 'UpdateAttrAt', @success OUT, 'xades:SignedProperties', 1, 'Id', 'xmldsig-cbd86598-e75e-4ae4-9a27-3b4ee2a789ac-signedprops'
EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedSignatureProperties|xades:SigningTime', 'TO BE GENERATED BY CHILKAT'
EXEC sp_OAMethod @object1, 'UpdateAttrAt', @success OUT, 'xades:SignedProperties|xades:SignedSignatureProperties|xades:SigningCertificateV2|xades:Cert|xades:CertDigest|ds:DigestMethod', 1, 'Algorithm', 'http://www.w3.org/2001/04/xmlenc#sha256'
EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedSignatureProperties|xades:SigningCertificateV2|xades:Cert|xades:CertDigest|ds:DigestValue', 'TO BE GENERATED BY CHILKAT'
EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedSignatureProperties|xades:SigningCertificateV2|xades:Cert|xades:IssuerSerialV2', 'TO BE GENERATED BY CHILKAT'
EXEC sp_OAMethod @object1, 'UpdateAttrAt', @success OUT, 'xades:SignedProperties|xades:SignedDataObjectProperties|xades:DataObjectFormat', 1, 'ObjectReference', '#xmldsig-cbd86598-e75e-4ae4-9a27-3b4ee2a789ac-ref0'
EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedDataObjectProperties|xades:DataObjectFormat|xades:Description', 'DFE Signature'
EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedDataObjectProperties|xades:DataObjectFormat|xades:ObjectIdentifier|xades:Identifier', 'efatura-uri'
EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedDataObjectProperties|xades:DataObjectFormat|xades:ObjectIdentifier|xades:DocumentationReferences|xades:DocumentationReference', 'https://efatura.cv'
EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedDataObjectProperties|xades:DataObjectFormat|xades:MimeType', 'text/xml'
EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedDataObjectProperties|xades:DataObjectFormat|xades:Encoding', 'UTF-8'
EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedDataObjectProperties|xades:CommitmentTypeIndication|xades:CommitmentTypeId|xades:Identifier', 'http://uri.etsi.org/01903/v1.2.2#ProofOfApproval'
EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedDataObjectProperties|xades:CommitmentTypeIndication|xades:CommitmentTypeId|xades:Description', 'Indicates that the signer has approved the content of the signed data object'
EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedDataObjectProperties|xades:CommitmentTypeIndication|xades:AllSignedDataObjects', ''
EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedDataObjectProperties|xades:CommitmentTypeIndication[1]|xades:CommitmentTypeId|xades:Identifier', 'http://uri.etsi.org/01903/v1.2.2#ProofOfOrigin'
EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedDataObjectProperties|xades:CommitmentTypeIndication[1]|xades:CommitmentTypeId|xades:Description', 'Indicates that the signer recognizes to have created, approved and sent the signed data object'
EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedDataObjectProperties|xades:CommitmentTypeIndication[1]|xades:ObjectReference', '#xmldsig-cbd86598-e75e-4ae4-9a27-3b4ee2a789ac-ref0'
EXEC sp_OAMethod @object1, 'UpdateChildContent', NULL, 'xades:SignedProperties|xades:SignedDataObjectProperties|xades:CommitmentTypeIndication[1]|xades:CommitmentTypeQualifiers|xades:CommitmentTypeQualifier', 'CV-FE-123456789'
EXEC sp_OAMethod @object1, 'GetXml', @sTmp0 OUT
EXEC sp_OAMethod @gen, 'AddObject', @success OUT, '', @sTmp0, '', ''
-- -------- Reference 1 --------
EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'CV2210818123456789000010100000000546735014601', 'sha256', '', '', ''
EXEC sp_OAMethod @gen, 'SetRefIdAttr', @success OUT, 'CV2210818123456789000010100000000546735014601', 'xmldsig-cbd86598-e75e-4ae4-9a27-3b4ee2a789ac-ref0'
-- -------- Reference 2 --------
EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'xmldsig-cbd86598-e75e-4ae4-9a27-3b4ee2a789ac-keyinfo', 'sha256', 'C14N', '', ''
-- -------- Reference 3 --------
EXEC sp_OAMethod @gen, 'AddObjectRef', @success OUT, 'xmldsig-cbd86598-e75e-4ae4-9a27-3b4ee2a789ac-signedprops', 'sha256', 'C14N', '', 'http://uri.etsi.org/01903#SignedProperties'
-- 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 @xmlToSign
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @object1
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,SubjectName,Certificate'
-- Load XML to be signed...
DECLARE @sbXml int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT
EXEC sp_OAMethod @xmlToSign, 'GetXmlSb', @success OUT, @sbXml
-- Note: The last keyword (Base64Cr13Entity) requires Chilkat v9.5.0.89 or later.
EXEC sp_OASetProperty @gen, 'Behaviors', 'IndentedSignature,Base64Cr13Entity,ForceAddEnvelopedSignatureTransform,Base64Cr13Entity'
-- 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 @xmlToSign
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @object1
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/signedXml.xml', 'utf-8', 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 @xmlToSign
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @object1
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 @xmlToSign
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @object1
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 @xmlToSign
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @object1
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @verifier
END
GO