![]() |
Chilkat • HOME • Android™ • AutoIt • C • C# • C++ • Chilkat2-Python • CkPython • Classic ASP • DataFlex • Delphi DLL • Go • Java • Node.js • Objective-C • PHP Extension • Perl • PowerBuilder • PowerShell • PureBasic • Ruby • SQL Server • Swift • Tcl • Unicode C • Unicode C++ • VB.NET • VBScript • Visual Basic 6.0 • Visual FoxPro • Xojo Plugin
(SQL Server) Create JPK VAT metadata XMLDemonstrates how to create the JPK VAT metadata XML (InitUpload) that will be signed using XADES.
-- 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 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 -- 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', '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 -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0 EXEC @hr = sp_OACreate 'Chilkat.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 -- 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, '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 -- Use "Chilkat_9_5_0.Zip" for versions of Chilkat < 10.0.0 EXEC @hr = sp_OACreate 'Chilkat.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 -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0 EXEC @hr = sp_OACreate 'Chilkat.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 -- Use "Chilkat_9_5_0.Prng" for versions of Chilkat < 10.0.0 EXEC @hr = sp_OACreate 'Chilkat.Prng', @prng OUT DECLARE @bdAesKey int -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0 EXEC @hr = sp_OACreate 'Chilkat.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 -- 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/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 -- Use "Chilkat_9_5_0.Rsa" for versions of Chilkat < 10.0.0 EXEC @hr = sp_OACreate 'Chilkat.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 |
© 2000-2025 Chilkat Software, Inc. All Rights Reserved.