(SQL Server) POST GovTalk XML to https://vrep1-t.cssz.cz/VREP/submission

Demonstrates how to POST a GovTalk XML message to https://vrep1-t.cssz.cz/VREP/submission

PDF Documentation is at http://www.cssz.cz/NR/rdonlyres/0ACDBB35-F78A-4B1A-8258-7249B55C788E/0/csszpodavacidotazovaciprotokol20110608.pdf

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- 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 &amp; 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 @success int

    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Gzip" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/govs/czech/pvpoj/DISCSSZ2019.cer'
    IF @success <> 1
      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
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    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 <> 1
      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 <> 1
      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
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    DECLARE @resp int
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'PostXml', @resp OUT, 'https://vrep1-t.cssz.cz/VREP/submission', @sTmp0, 'utf-8'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      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
        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 @resp


    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


END
GO