SQL Server
SQL Server
Create Signed SOAP XML for Albanian Fiscalization Service
See more XAdES Examples
This example is created for a customer that needs to send signed billing/invoicing SOAP XML requests to http://efiskalizimi-test.tatime.gov.al:80/FiscalizationServiceThe example demonstrates how to create and signed the SOAP XML message that is to be sent.
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.
-- --------------------------------------------------------------------------------
-- Also see Chilkat's Online WSDL Code Generator
-- to generate code and SOAP Request and Response XML for each operation in a WSDL.
-- --------------------------------------------------------------------------------
-- We want to build and send a signed SOAP XML that looks like the following:
--
-- <?xml version="1.0" encoding="utf-8"?>
-- <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
-- <SOAP-ENV:Header/>
-- <SOAP-ENV:Body>
-- <RegisterWTNRequest xmlns="https://eFiskalizimi.tatime.gov.al/FiscalizationService/schema" xmlns:ns2="http://www.w3.org/2000/09/xmldsig#" Id="Request">
-- <Header SendDateTime="2019-09-03T14:19:01+02:00" UUID="42db3af5-0d9f-4dea-95b4-4b947ab8d8e7"/>
-- <WTN BusinUnit="bb123bb123" DateTimeCreated="2019-09-03T14:19:01+02:00" DestinAddr="Destination address"
-- DestinCity="Destination city" IsAfterDel="false" OperatorCode="oo123oo123"
-- SoftNum="ss123ss123" StartAddr="Start address" StartCity="Start city" TransDate="2019-09-03T14:19:01+02:00"
-- VehPlates="AA0000AA" WTNIC="5C5E58580D0A24E1F7A5E5E011929511" WTNICSignature="82D69C38206D ... F5FCA48" WTNNum="12345678901">
-- <Issuer NUIS="I12345678I" Name="Issuer name"/>
-- <Items>
-- <I C="501234567890" N="Item name" Q="1.0" U="piece"/>
-- </Items>
-- </WTN>
-- <Signature xmlns="http://www.w3.org/2000/09/xmldsig#">
-- <SignedInfo>
-- <CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/>
-- <SignatureMethod Algorithm="http://www.w3.org/2001/04/xmldsig-more#rsa-sha256"/>
-- <Reference URI="#Request">
-- <Transforms>
-- <Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"/>
-- <Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/>
-- </Transforms>
-- <DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/>
-- <DigestValue>aNa0r4RW04BlsAciTUrWHGW9PADskl2op315BAQ0hTg=</DigestValue>
-- </Reference>
-- </SignedInfo>
-- <SignatureValue>4/j/d4j/5xCJ2YUP+XTC6li0B94...........................KESd38NT5+puArBcNgLYIjLx/dh6Q==</SignatureValue>
-- <KeyInfo>
-- <X509Data>
-- <X509Certificate>MIIE6TCCAtGgAwIBAgICEA8wDQYJKoZIhvcNA......................uoqWsSuLmA==</X509Certificate>
-- </X509Data>
-- </KeyInfo>
-- </Signature>
-- </RegisterWTNRequest>
-- </SOAP-ENV:Body>
-- </SOAP-ENV:Envelope>
SELECT @success = 1
-- Create the XML to be signed...
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', 'SOAP-ENV:Envelope'
EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:SOAP-ENV', 'http://schemas.xmlsoap.org/soap/envelope/'
EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'SOAP-ENV:Header', ''
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest', 1, 'xmlns', 'https://eFiskalizimi.tatime.gov.al/FiscalizationService/schema'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest', 1, 'xmlns:ns2', 'http://www.w3.org/2000/09/xmldsig#'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest', 1, 'Id', 'Request'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|Header', 1, 'SendDateTime', '2019-09-03T14:19:01+02:00'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|Header', 1, 'UUID', '42db3af5-0d9f-4dea-95b4-4b947ab8d8e7'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN', 1, 'BusinUnit', 'bb123bb123'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN', 1, 'DateTimeCreated', '2019-09-03T14:19:01+02:00'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN', 1, 'DestinAddr', 'Destination address'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN', 1, 'DestinCity', 'Destination city'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN', 1, 'IsAfterDel', 'false'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN', 1, 'OperatorCode', 'oo123oo123'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN', 1, 'SoftNum', 'ss123ss123'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN', 1, 'StartAddr', 'Start address'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN', 1, 'StartCity', 'Start city'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN', 1, 'TransDate', '2019-09-03T14:19:01+02:00'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN', 1, 'VehPlates', 'AA0000AA'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN', 1, 'WTNIC', '5C5E58580D0A24E1F7A5E5E011929511'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN', 1, 'WTNICSignature', '82D69C38206D ... F5FCA48'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN', 1, 'WTNNum', '12345678901'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN|Issuer', 1, 'NUIS', 'I12345678I'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN|Issuer', 1, 'Name', 'Issuer name'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN|Items|I', 1, 'C', '501234567890'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN|Items|I', 1, 'N', 'Item name'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN|Items|I', 1, 'Q', '1.0'
EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'SOAP-ENV:Body|RegisterWTNRequest|WTN|Items|I', 1, 'U', 'piece'
DECLARE @gen int
EXEC @hr = sp_OACreate 'Chilkat.XmlDSigGen', @gen OUT
EXEC sp_OASetProperty @gen, 'SigLocation', 'SOAP-ENV:Envelope|SOAP-ENV:Body|RegisterWTNRequest'
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', 'EXCL_C14N'
EXEC sp_OASetProperty @gen, 'SignedInfoDigestMethod', 'sha256'
-- -------- Reference 1 --------
EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'Request', 'sha256', 'EXCL_C14N', '', ''
-- 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 = 0
BEGIN
EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xmlToSign
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'
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 @xmlToSign, 'GetXmlSb', @success OUT, @sbXml
EXEC sp_OASetProperty @gen, 'Behaviors', 'CompactSignedXml,ForceAddEnvelopedSignatureTransform'
-- Sign the XML...
EXEC sp_OAMethod @gen, 'CreateXmlDSigSb', @success OUT, @sbXml
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xmlToSign
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @sbXml
RETURN
END
-- -----------------------------------------------
-- Now to send the SOAP request....
-- Note: Chilkat did not actually test this by sending the request to the server.
-- The example is a best-guess for how to do it...
DECLARE @strXml nvarchar(4000)
EXEC sp_OAMethod @sbXml, 'GetAsString', @strXml OUT
DECLARE @responseStatusCode int
-- We'll need to add this in the HTTP header:
-- SOAPAction: "https://eFiskalizimi.tatime.gov.al/FiscalizationService/RegisterWTN"
-- Note: This is for the RegisterWTN request.
-- You may wish to load the WSDL at https://efiskalizimi-test.tatime.gov.al/FiscalizationService-v1/FiscalizationService.wsdl into the SoapUI application to see
-- the SOAP requests..
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'SOAPAction', 'https://eFiskalizimi.tatime.gov.al/FiscalizationService/RegisterWTN'
-- The testing endpoint for this soap service is likely:
DECLARE @endPoint nvarchar(4000)
SELECT @endPoint = 'https://efiskalizimi-test.tatime.gov.al/FiscalizationService-v1'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpStr', @success OUT, 'POST', @endPoint, @strXml, 'utf-8', 'text/xml', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xmlToSign
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @responseStatusCode OUT
-- Check that the responseStatusCode equals 200...
PRINT 'Response Status Code: ' + @responseStatusCode
-- Examine the exact HTTP header sent with the POST like this:
PRINT 'LastHeader:'
EXEC sp_OAGetProperty @http, 'LastHeader', @sTmp0 OUT
PRINT @sTmp0
-- Examine the XML returned by the web service:
PRINT 'XML Response:'
DECLARE @xmlResp int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlResp OUT
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @xmlResp, 'LoadXml', @success OUT, @sTmp0
EXEC sp_OAMethod @xmlResp, 'GetXml', @sTmp0 OUT
PRINT @sTmp0
-- Use this online tool to generate parsing code from response XML:
-- Generate Parsing Code from XML
EXEC @hr = sp_OADestroy @xmlToSign
EXEC @hr = sp_OADestroy @gen
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @sbXml
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @xmlResp
END
GO