(SQL Server) Create JPK VAT metadata XML

Demonstrates how to create the JPK VAT metadata XML (InitUpload) that will be signed using XADES.

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
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- First build an InitUpload XML template

    -- Use this online tool to generate the code from the sample XML below: 
    -- Generate Code to Create XML

    -- <InitUpload xmlns="http://e-dokumenty.mf.gov.pl">
    --     <DocumentType>JPK</DocumentType>
    --     <Version>01.02.01.20160617</Version>
    --     <EncryptionKey algorithm="RSA" encoding="Base64" mode="ECB" padding="PKCS#1">F9EhKFec...uWqAWUIg==</EncryptionKey>
    --     <DocumentList>
    --         <Document>
    --             <FormCode schemaVersion="1-1" systemCode="JPK_VAT (3)">JPK_VAT</FormCode>
    --             <FileName>JPK_VAT_3_v1-1_20181201.xml</FileName>
    --             <ContentLength>8736</ContentLength>
    --             <HashValue algorithm="SHA-256" encoding="Base64">JFDI1pItwh6dj/Xe1uts/x61qnjZ4DLHpkZMhmf1oKQ=</HashValue>
    --             <FileSignatureList filesNumber="1">
    --                 <Packaging>
    --                     <SplitZip mode="zip" type="split"/>
    --                 </Packaging>
    --                 <Encryption>
    --                     <AES block="16" mode="CBC" padding="PKCS#7" size="256">
    --                         <IV bytes="16" encoding="Base64">z64oN9zXHt1+S3XACRSCYw==</IV>
    --                     </AES>
    --                 </Encryption>
    --                 <FileSignature>
    --                     <OrdinalNumber>1</OrdinalNumber>
    --                     <FileName>JPK_VAT_3_v1-1_20181201-000.xml.zip.aes</FileName>
    --                     <ContentLength>16</ContentLength>
    --                     <HashValue algorithm="MD5" encoding="Base64">5NX0q1935fvMjLFV7E1yDw==</HashValue>
    --                 </FileSignature>
    --             </FileSignatureList>
    --         </Document>
    --     </DocumentList>
    -- </InitUpload>

    DECLARE @success int

    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Xml', @xml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @xml, 'Tag', 'InitUpload'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns', 'http://e-dokumenty.mf.gov.pl'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentType', 'JPK'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Version', '01.02.01.20160617'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'EncryptionKey', 1, 'algorithm', 'RSA'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'EncryptionKey', 1, 'encoding', 'Base64'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'EncryptionKey', 1, 'mode', 'ECB'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'EncryptionKey', 1, 'padding', 'PKCS#1'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'EncryptionKey', 'TO BE DETERMINED'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FormCode', 1, 'schemaVersion', '1-1'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FormCode', 1, 'systemCode', 'JPK_VAT (3)'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|FormCode', 'JPK_VAT'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|FileName', 'JPK_VAT_3_v1-1_20181201.xml'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|ContentLength', '9999'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|HashValue', 1, 'algorithm', 'SHA-256'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|HashValue', 1, 'encoding', 'Base64'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|HashValue', 'TO BE DETERMINED'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList', 1, 'filesNumber', '1'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|Packaging|SplitZip', 1, 'mode', 'zip'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|Packaging|SplitZip', 1, 'type', 'split'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|Encryption|AES', 1, 'block', '16'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|Encryption|AES', 1, 'mode', 'CBC'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|Encryption|AES', 1, 'padding', 'PKCS#7'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|Encryption|AES', 1, 'size', '256'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|Encryption|AES|IV', 1, 'bytes', '16'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|Encryption|AES|IV', 1, 'encoding', 'Base64'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|FileSignatureList|Encryption|AES|IV', 'TO BE DETERMINED'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|FileSignatureList|FileSignature|OrdinalNumber', '1'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|FileSignatureList|FileSignature|FileName', 'JPK_VAT_3_v1-1_20181201-000.xml.zip.aes'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|FileSignatureList|FileSignature|ContentLength', '9999'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|FileSignature|HashValue', 1, 'algorithm', 'MD5'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'DocumentList|Document|FileSignatureList|FileSignature|HashValue', 1, 'encoding', 'Base64'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|FileSignatureList|FileSignature|HashValue', 'TO BE DETERMINED'

    -- ------------------------------------------------------------
    -- Step 1: Load our JPK_VAT XML and update the DocumentList|Document|HashValue
    -- and DocumentList|Document|ContentLength
    DECLARE @bdXml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bdXml OUT

    EXEC sp_OAMethod @bdXml, 'LoadFile', @success OUT, 'qa_data/xml_dsig/jpk_vat/JPK_VAT_3_v1-1_20181201-000.xml'
    IF @success <> 1
      BEGIN

        PRINT 'Failed to load XML file.'
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @bdXml
        RETURN
      END

    EXEC sp_OAGetProperty @bdXml, 'NumBytes', @iTmp0 OUT
    EXEC sp_OAMethod @xml, 'UpdateChildContentInt', NULL, 'DocumentList|Document|ContentLength', @iTmp0

    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Crypt2', @crypt OUT

    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
    EXEC sp_OAMethod @crypt, 'HashBdENC', @sTmp0 OUT, @bdXml
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|HashValue', @sTmp0

    -- ------------------------------------------------------------
    -- Step 2: Create a Zip archive containing the XML.
    DECLARE @zip int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Zip', @zip OUT

    -- The filename we pass here doesn't matter because we won't actually be creating a .zip file.
    EXEC sp_OAMethod @zip, 'NewZip', @success OUT, 'anything.zip'
    DECLARE @e int
    EXEC sp_OAMethod @zip, 'AppendBd', @e OUT, 'JPK_VAT_3_v1-1_20181201-000.xml', @bdXml
    EXEC @hr = sp_OADestroy @e

    -- Write the .zip file to a BinData object.
    DECLARE @bdZip int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bdZip OUT

    EXEC sp_OAMethod @zip, 'WriteBd', @success OUT, @bdZip

    -- ------------------------------------------------------------
    -- Step 3: Generate a random 256-bit AES key (32-bytes)
    DECLARE @prng int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Prng', @prng OUT

    DECLARE @bdAesKey int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bdAesKey OUT

    EXEC sp_OAMethod @prng, 'GenRandomBd', @success OUT, 32, @bdAesKey
    DECLARE @ivBytes nvarchar(4000)
    EXEC sp_OAMethod @prng, 'GenRandom', @ivBytes OUT, 16, 'base64'

    -- Store the IV (base64 string) in the XML.
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|FileSignatureList|Encryption|AES|IV', @ivBytes

    -- ------------------------------------------------------------
    -- Step 4: AES encrypt our zip archive (the contents of bdZip)
    EXEC sp_OASetProperty @crypt, 'CipherMode', 'cbc'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 256
    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'aes'
    EXEC sp_OASetProperty @crypt, 'PaddingScheme', 0
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, @ivBytes, 'base64'
    EXEC sp_OAMethod @bdAesKey, 'GetEncoded', @sTmp0 OUT, 'base64'
    EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, @sTmp0, 'base64'
    -- AES by definition has a block size of 16.
    EXEC sp_OAMethod @crypt, 'EncryptBd', @success OUT, @bdZip

    -- bdZip now contains the AES encrypted data. 
    -- Note: This is NOT the same as a zip where the contents are AES encrypted.
    -- In that case, we have an unencrypted zip structure with AES encrypted files within.
    -- In our case, the entire zip file image is encrypted.

    -- Save the bdZip to a file.  This is what will get sent to e-dokumenty.mf.gov.pl
    EXEC sp_OAMethod @bdZip, 'WriteFile', @success OUT, 'qa_output/JPK_VAT_3_v1-1_20181201-000.xml.zip.aes'
    EXEC sp_OAGetProperty @bdZip, 'NumBytes', @iTmp0 OUT
    EXEC sp_OAMethod @xml, 'UpdateChildContentInt', NULL, 'DocumentList|Document|FileSignatureList|FileSignature|ContentLength', @iTmp0

    -- ------------------------------------------------------------
    -- Step 4: RSA Encrypt the AES key using the public key certificate provided by the Ministry of Finance
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/pem/mf_public_rsa.pem'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @bdXml
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @zip
        EXEC @hr = sp_OADestroy @bdZip
        EXEC @hr = sp_OADestroy @prng
        EXEC @hr = sp_OADestroy @bdAesKey
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END
    DECLARE @pubKey int
    EXEC sp_OAMethod @cert, 'ExportPublicKey', @pubKey OUT
    DECLARE @rsa int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Rsa', @rsa OUT

    EXEC sp_OAMethod @rsa, 'ImportPublicKeyObj', @success OUT, @pubKey
    EXEC @hr = sp_OADestroy @pubKey

    EXEC sp_OASetProperty @rsa, 'EncodingMode', 'base64'
    EXEC sp_OASetProperty @rsa, 'LittleEndian', 0
    -- in-place RSA encrypt the contents of bdAesKey.
    EXEC sp_OAMethod @rsa, 'EncryptBd', @success OUT, @bdAesKey, 0
    EXEC sp_OAMethod @bdAesKey, 'GetEncoded', @sTmp0 OUT, 'base64'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'EncryptionKey', @sTmp0

    -- Step 5: We forgot to get the MD5 hash of the AES encrypted zip.
    -- (I'm assuming we need the MD5 of the encrypted zip as opposed to the MD5 of the pre-encrypted zip..)
    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'md5'
    EXEC sp_OAMethod @crypt, 'HashBdENC', @sTmp0 OUT, @bdZip
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DocumentList|Document|FileSignatureList|FileSignature|HashValue', @sTmp0

    -- At this point, the XML is prepared and the AES encrypted image of the zip file is written
    -- to a file (and also in bdZip).
    DECLARE @finalXml nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetXml', @finalXml OUT

    PRINT @finalXml

    EXEC sp_OAMethod @xml, 'SaveXml', @success OUT, 'qa_output/jpk_vat.xml'


    PRINT 'Finished.'

    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @bdXml
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @zip
    EXEC @hr = sp_OADestroy @bdZip
    EXEC @hr = sp_OADestroy @prng
    EXEC @hr = sp_OADestroy @bdAesKey
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @rsa


END
GO