(SQL Server) IMAP Read PEC Email of Aruba and Extract the Invoice XML Files

IMAP Read PEC Email of Aruba and Extract the Invoice XML Files.

Note: This example requires Chilkat v11.0.0 or greater.

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 @iTmp1 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 requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

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

    -- Connect using TLS.
    EXEC sp_OASetProperty @imap, 'Ssl', 1
    EXEC sp_OASetProperty @imap, 'Port', 993
    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.example.com'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Authenticate
    EXEC sp_OAMethod @imap, 'Login', @success OUT, 'email_account_login', 'email_account_password'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Select an IMAP mailbox
    EXEC sp_OAMethod @imap, 'SelectMailbox', @success OUT, 'Inbox/postacert'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Search for messages having "POSTA CERTIFICATA: Invio File" in the subject.
    DECLARE @fetchUids int
    SELECT @fetchUids = 1
    DECLARE @messageSet int
    EXEC @hr = sp_OACreate 'Chilkat.MessageSet', @messageSet OUT

    EXEC sp_OAMethod @imap, 'QueryMbx', @success OUT, 'SUBJECT "POSTA CERTIFICATA: Invio File"', @fetchUids, @messageSet
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @messageSet
        RETURN
      END

    EXEC sp_OAGetProperty @messageSet, 'Count', @iTmp0 OUT
    IF @iTmp0 < 1
      BEGIN

        PRINT 'No messages found.'
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @messageSet
        RETURN
      END
    -- For our example, we'll process only the 1st email in the messageSet.
    -- Download the MIME source of the email into a StringBuilder object.
    DECLARE @email int
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT

    EXEC sp_OAMethod @messageSet, 'GetId', @iTmp0 OUT, 0
    EXEC sp_OAGetProperty @messageSet, 'HasUids', @iTmp1 OUT
    EXEC sp_OAMethod @imap, 'FetchEmail', @success OUT, 0, @iTmp0, @iTmp1, @email
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @messageSet
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    -- The email should contain an attached email.
    -- The Invoice XML is contained within the attached email.
    DECLARE @attachedEmail int
    EXEC @hr = sp_OACreate 'Chilkat.Email', @attachedEmail OUT

    EXEC sp_OAMethod @email, 'GetAttachedEmail', @success OUT, 0, @attachedEmail
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @messageSet
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @attachedEmail
        RETURN
      END

    -- Get the MIME of the attached email..
    DECLARE @sbMime int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbMime OUT

    EXEC sp_OAMethod @attachedEmail, 'GetMimeSb', @success OUT, @sbMime

    -- Load it into a Chilkat MIME object.
    DECLARE @mime int
    EXEC @hr = sp_OACreate 'Chilkat.Mime', @mime OUT

    EXEC sp_OAMethod @mime, 'LoadMimeSb', @success OUT, @sbMime

    -- Examine the structure of the MIME.
    EXEC sp_OAMethod @mime, 'GetStructure', @sTmp0 OUT, 'text'
    PRINT @sTmp0

    -- The MIME has this structure:
    -- multipart/mixed
    --     application/octet-stream
    --     application/octet-stream     <-- This is where the XML is contained.   It is within an opaque signature.
    --     text/plain

    -- The 2nd application/octet-stream MIME header looks like this:

    -- ------=_Part_329673_-1348225228.1579889273592
    -- Content-Type: application/octet-stream; name="SM99999_99aaa.xml.p7m"
    -- Content-Transfer-Encoding: base64
    -- Content-Disposition: attachment; filename="SM99999_99aaa.xml.p7m"

    -- Let's get the binary content of the .p7m
    DECLARE @bdP7m int
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdP7m OUT

    -- Get the 2nd sub-part, at index 1 because index 0 is the 1st sub-part.

    DECLARE @mimeP7m int
    EXEC @hr = sp_OACreate 'Chilkat.Mime', @mimeP7m OUT

    EXEC sp_OAMethod @mime, 'PartAt', @success OUT, 1, @mimeP7m
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mime, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @messageSet
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @attachedEmail
        EXEC @hr = sp_OADestroy @sbMime
        EXEC @hr = sp_OADestroy @mime
        EXEC @hr = sp_OADestroy @bdP7m
        EXEC @hr = sp_OADestroy @mimeP7m
        RETURN
      END

    EXEC sp_OAGetProperty @mime, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

        PRINT 'Failed to get 2nd sub-part. Perhaps the MIME does not have a 2nd sub-part?'
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @messageSet
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @attachedEmail
        EXEC @hr = sp_OADestroy @sbMime
        EXEC @hr = sp_OADestroy @mime
        EXEC @hr = sp_OADestroy @bdP7m
        EXEC @hr = sp_OADestroy @mimeP7m
        RETURN
      END
    EXEC sp_OAMethod @mimeP7m, 'GetBodyBd', @success OUT, @bdP7m

    -- Verify the signature, which also extracts the content contained within the opaque signature.
    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    EXEC sp_OAMethod @crypt, 'OpaqueVerifyBd', @success OUT, @bdP7m
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @messageSet
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @attachedEmail
        EXEC @hr = sp_OADestroy @sbMime
        EXEC @hr = sp_OADestroy @mime
        EXEC @hr = sp_OADestroy @bdP7m
        EXEC @hr = sp_OADestroy @mimeP7m
        EXEC @hr = sp_OADestroy @crypt
        RETURN
      END

    -- The bdP7m now contains the Invoice XML.
    -- Load it into an XML object.
    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OAMethod @xml, 'LoadBd', @success OUT, @bdP7m, 1

    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- We have XML such as the following:
    -- 
    -- <?xml version="1.0" encoding="windows-1252"?>
    -- <?xml-stylesheet type="text/xsl" href="fatturapa_v1.2.xsl"?>
    -- <p:FatturaElettronica versione="FPR12" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" 
    --     xmlns:p="http://ivaservizi.agenziaentrate.gov.it/docs/xsd/fatture/v1.2" 
    --     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    --     <FatturaElettronicaHeader>
    -- 	...
    --     </FatturaElettronicaHeader>
    --     <FatturaElettronicaBody>
    -- 	...
    --     </FatturaElettronicaBody>
    -- </p:FatturaElettronica>

    -- Use this online tool to generate parsing code from sample XML: 
    -- Generate Parsing Code from XML


    PRINT 'success.'

    EXEC @hr = sp_OADestroy @imap
    EXEC @hr = sp_OADestroy @messageSet
    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @attachedEmail
    EXEC @hr = sp_OADestroy @sbMime
    EXEC @hr = sp_OADestroy @mime
    EXEC @hr = sp_OADestroy @bdP7m
    EXEC @hr = sp_OADestroy @mimeP7m
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @xml


END
GO