SQL Server
SQL Server
POST GovTalk XML to https://vrep1-t.cssz.cz/VREP/submission
See more HTTP Misc Examples
Demonstrates how to POST a GovTalk XML message to https://vrep1-t.cssz.cz/VREP/submissionPDF Documentation is at http://www.cssz.cz/NR/rdonlyres/0ACDBB35-F78A-4B1A-8258-7249B55C788E/0/csszpodavacidotazovaciprotokol20110608.pdf
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 requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- First prepare the XML body that is to be GZip compressed and then encrypted.
-- Build the following XML..
-- Use the online tool at http://tools.chilkat.io/xmlCreate.cshtml
-- to generate the XML creation code that follows this XML.
-- <?xml version="1.0" encoding="utf-8" ?>
-- <pvpoj xmlns="http://schemas.cssz.cz/POJ/PVPOJ2016">
-- <prehled verze="2016.0" typPrehledu="N">
-- <okres>
-- <kodOSSZ>119</kodOSSZ>
-- <nazevOSSZ>Praha 9</nazevOSSZ>
-- </okres>
-- <obdobi>
-- <mesic>1</mesic>
-- <rok>2018</rok>
-- </obdobi>
-- <zamestnavatel>
-- <vs>9992002999</vs>
-- <IC>49710999</IC>
-- <nazev>SIT & KHASO v.o.s.</nazev>
-- <adresa>
-- <ulice>Na sp�leni�ti</ulice>
-- <cisloDomu>517</cisloDomu>
-- <obec>Praha 9</obec>
-- <PSC>19032</PSC>
-- <stat>CZ</stat>
-- </adresa>
-- </zamestnavatel>
-- <pojistne>
-- <uhrnVymerovacichZakladu>1104</uhrnVymerovacichZakladu>
-- <pojistneZamestnance>175</pojistneZamestnance>
-- <pojistneZamestnavatele>2826</pojistneZamestnavatele>
-- <pojistneCelkem>4301</pojistneCelkem>
-- </pojistne>
-- <platebniUdaje>
-- <bankaCisloUctu>012345789/0800</bankaCisloUctu>
-- </platebniUdaje>
-- <pracovnik>
-- <jmeno>Matt</jmeno>
-- <prijmeni>Smith</prijmeni>
-- <telefon>606796999</telefon>
-- <email>admin@chilkatsoft.com</email>
-- </pracovnik>
-- <datumVyplneni>2018-03-02</datumVyplneni>
-- <poznamka />
-- </prehled>
-- </pvpoj>
DECLARE @xml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @xml, 'Tag', 'pvpoj'
EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns', 'http://schemas.cssz.cz/POJ/PVPOJ2016'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'prehled', 1, 'verze', '2016.0'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'prehled', 1, 'typPrehledu', 'N'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|okres|kodOSSZ', '119'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|okres|nazevOSSZ', 'Praha 9'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|obdobi|mesic', '1'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|obdobi|rok', '2018'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|zamestnavatel|vs', '9992002999'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|zamestnavatel|IC', '49710999'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|zamestnavatel|nazev', 'SIT & KHASO v.o.s.'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|zamestnavatel|adresa|ulice', 'Na sp�leni�ti'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|zamestnavatel|adresa|cisloDomu', '517'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|zamestnavatel|adresa|obec', 'Praha 9'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|zamestnavatel|adresa|PSC', '19032'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|zamestnavatel|adresa|stat', 'CZ'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|pojistne|uhrnVymerovacichZakladu', '1104'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|pojistne|pojistneZamestnance', '175'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|pojistne|pojistneZamestnavatele', '2826'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|pojistne|pojistneCelkem', '4301'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|platebniUdaje|bankaCisloUctu', '012345789/0800'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|pracovnik|jmeno', 'Matt'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|pracovnik|prijmeni', 'Smith'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|pracovnik|telefon', '606796999'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|pracovnik|email', 'admin@chilkatsoft.com'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|datumVyplneni', '2018-03-02'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'prehled|poznamka', ''
EXEC sp_OASetProperty @xml, 'EmitCompact', 1
EXEC sp_OASetProperty @xml, 'EmitXmlDecl', 0
-- Emit the XML into bd.
DECLARE @bd int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT
EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
EXEC sp_OAMethod @bd, 'AppendString', @success OUT, @sTmp0, 'utf-8'
-- GZip compress the bd contents.
DECLARE @gzip int
EXEC @hr = sp_OACreate 'Chilkat.Gzip', @gzip OUT
-- The Filename is not an actual file on disk, but is just the filename string stored in the gzip compressed format.
EXEC sp_OASetProperty @gzip, 'Filename', 'pvpoj.xml'
EXEC sp_OAMethod @gzip, 'CompressBd', @success OUT, @bd
-- Encrypt the contents using the government provided cert.
DECLARE @cert int
EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/govs/czech/pvpoj/DISCSSZ2019.cer'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @bd
EXEC @hr = sp_OADestroy @gzip
EXEC @hr = sp_OADestroy @cert
RETURN
END
DECLARE @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'pki'
EXEC sp_OASetProperty @crypt, 'Pkcs7CryptAlg', 'aes'
EXEC sp_OASetProperty @crypt, 'KeyLength', 256
EXEC sp_OAMethod @crypt, 'SetEncryptCert', @success OUT, @cert
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @bd
EXEC @hr = sp_OADestroy @gzip
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @crypt
RETURN
END
EXEC sp_OAMethod @crypt, 'EncryptBd', @success OUT, @bd
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @bd
EXEC @hr = sp_OADestroy @gzip
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @crypt
RETURN
END
-- At this point, bd contains the gzip compressed / encrypted XML.
-- Build the following GovTalkMessage XML:
-- <?xml version="1.0" encoding="utf-8"?>
-- <GovTalkMessage xmlns="http://www.govtalk.gov.uk/CM/envelope">
-- <EnvelopeVersion>2.0</EnvelopeVersion>
-- <Header>
-- <MessageDetails>
-- <Class>CSSZ_PVPOJ</Class>
-- <Qualifier>request</Qualifier>
-- <Function>submit</Function>
-- <TransactionID />
-- <AuditID />
-- <CorrelationID />
-- <ResponseEndPoint PollInterval="0" />
-- <Transformation>XML</Transformation>
-- <GatewayTest />
-- <GatewayTimestamp />
-- </MessageDetails>
-- <SenderDetails>
-- <IDAuthentication>
-- <SenderID />
-- <Authentication>
-- <Method>clear</Method>
-- <Role />
-- <Value />
-- </Authentication>
-- </IDAuthentication>
-- <X509Certificate />
-- <EmailAddress>admin@chilkatsoft.com</EmailAddress>
-- </SenderDetails>
-- </Header>
-- <GovTalkDetails>
-- <Keys>
-- <Key Type="vars">9992888999</Key>
-- </Keys>
-- <GatewayAdditions />
-- </GovTalkDetails>
-- <Body>
-- <Message xmlns="http://www.cssz.cz/XMLSchema/envelope" version="1.2" eType="PVPOJ16">
-- <Header>
-- <Signature xmlns:dt="urn:schemas-microsoft-com:datatypes" dt:dt="bin.base64">Signature goes here</Signature>
-- <Vendor productName="Abc xyz. s r.o. JKL .NET Client" version="6.0.2.0" />
-- </Header>
-- <Body xmlns:dt="urn:schemas-microsoft-com:datatypes" encrypted="yes" contentEncoding="gzip" dt:dt="bin.base64">Base64 goes here
-- </Body>
-- </Message>
-- </Body>
-- </GovTalkMessage>
--
-- Use the online tool at http://tools.chilkat.io/xmlCreate.cshtml
-- to generate this code.
EXEC sp_OAMethod @xml, 'Clear', NULL
EXEC sp_OASetProperty @xml, 'Tag', 'GovTalkMessage'
EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns', 'http://www.govtalk.gov.uk/CM/envelope'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'EnvelopeVersion', '2.0'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Header|MessageDetails|Class', 'CSSZ_PVPOJ'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Header|MessageDetails|Qualifier', 'request'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Header|MessageDetails|Function', 'submit'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Header|MessageDetails|TransactionID', ''
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Header|MessageDetails|AuditID', ''
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Header|MessageDetails|CorrelationID', ''
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'Header|MessageDetails|ResponseEndPoint', 1, 'PollInterval', '0'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Header|MessageDetails|Transformation', 'XML'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Header|MessageDetails|GatewayTest', ''
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Header|MessageDetails|GatewayTimestamp', ''
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Header|SenderDetails|IDAuthentication|SenderID', ''
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Header|SenderDetails|IDAuthentication|Authentication|Method', 'clear'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Header|SenderDetails|IDAuthentication|Authentication|Role', ''
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Header|SenderDetails|IDAuthentication|Authentication|Value', ''
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Header|SenderDetails|X509Certificate', ''
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Header|SenderDetails|EmailAddress', 'admin@chilkatsoft.com'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'GovTalkDetails|Keys|Key', 1, 'Type', 'vars'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'GovTalkDetails|Keys|Key', '9992888999'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'GovTalkDetails|GatewayAdditions', ''
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'Body|Message', 1, 'xmlns', 'http://www.cssz.cz/XMLSchema/envelope'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'Body|Message', 1, 'version', '1.2'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'Body|Message', 1, 'eType', 'PVPOJ16'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'Body|Message|Header|Signature', 1, 'xmlns:dt', 'urn:schemas-microsoft-com:datatypes'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'Body|Message|Header|Signature', 1, 'dt:dt', 'bin.base64'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Body|Message|Header|Signature', 'Signature goes here'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'Body|Message|Header|Vendor', 1, 'productName', 'Abc xyz. s r.o. JKL .NET Client'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'Body|Message|Header|Vendor', 1, 'version', '6.0.2.0'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'Body|Message|Body', 1, 'xmlns:dt', 'urn:schemas-microsoft-com:datatypes'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'Body|Message|Body', 1, 'encrypted', 'yes'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'Body|Message|Body', 1, 'contentEncoding', 'gzip'
EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'Body|Message|Body', 1, 'dt:dt', 'bin.base64'
-- Insert the contents of bd here:
EXEC sp_OAMethod @bd, 'GetEncoded', @sTmp0 OUT, 'base64'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Body|Message|Body', @sTmp0
-- Show the XML to be sent to https://vrep1-t.cssz.cz/VREP/submission
EXEC sp_OASetProperty @xml, 'EmitCompact', 0
EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OASetProperty @xml, 'EmitCompact', 1
-- ----------------------------------------------------------
-- POST the XML to https://vrep1-t.cssz.cz/VREP/submission
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
EXEC sp_OAMethod @http, 'HttpStr', @success OUT, 'POST', 'https://vrep1-t.cssz.cz/VREP/submission', @sTmp0, 'utf-8', 'application/xml', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @bd
EXEC @hr = sp_OADestroy @gzip
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
RETURN
END
-- Examine the response status code (200 = success)
DECLARE @statusCode int
EXEC sp_OAGetProperty @resp, 'StatusCode', @statusCode OUT
PRINT 'Response status code = ' + @statusCode
-- The response body should be XML.
-- Load it into a Chilkat XML object to get it in indented format for viewing..
EXEC sp_OAMethod @xml, 'Clear', NULL
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @sTmp0
EXEC sp_OASetProperty @xml, 'EmitCompact', 0
EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
PRINT @sTmp0
-- Output is:
-- <GovTalkMessage xmlns="http://www.govtalk.gov.uk/CM/envelope">
-- <EnvelopeVersion>2.0</EnvelopeVersion>
-- <Header>
-- <MessageDetails>
-- <Class>CSSZ_PVPOJ</Class>
-- <Qualifier>acknowledgement</Qualifier>
-- <Function>submit</Function>
-- <TransactionID />
-- <CorrelationID>1B6336D5ACE949CDB944424CB2D54401</CorrelationID>
-- <ResponseEndPoint PollInterval="60">https://vrep1-t.cssz.cz/VREP/poll</ResponseEndPoint>
-- <GatewayTimestamp>2018-07-02T22:23:41.310</GatewayTimestamp>
-- </MessageDetails>
-- <SenderDetails />
-- </Header>
-- <GovTalkDetails>
-- <Keys />
-- </GovTalkDetails>
-- <Body>
-- <Signature xmlns="http://www.podani.gov.cz/TxE/timestamp" Version="1.0">
-- <DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1" />
-- <TimeStamp>
-- <date>20180702</date>
-- <time>22:23:41</time>
-- </TimeStamp>
-- <SignatureValue>MIIORwYJKoZIhvcNAQcCo ... bBNE6MeXmk2siMuvY=</SignatureValue>
-- </Signature>
-- </Body>
-- </GovTalkMessage>
--
-- Hint: Use the online tool at http://tools.chilkat.io/xmlParse.cshtml
-- to generate XML parsing code based on a sample response..
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @bd
EXEC @hr = sp_OADestroy @gzip
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
END
GO