(SQL Server) Convert MIME to XML

Demonstrates how to convert MIME to XML, and how to access the MIME parts and data from XML.

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

    DECLARE @mime int
    -- Use "Chilkat_9_5_0.Mime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Mime', @mime OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- The contents of this MIME file are shown below, 
    -- and is also available at https://www.chilkatsoft.com/sampleMime.txt
    DECLARE @success int
    EXEC sp_OAMethod @mime, 'LoadMimeFile', @success OUT, 'qa_data/mime/sampleMime.txt'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mime, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mime
        RETURN
      END

    -- The MIME used in this example has the following nested structure
    -- 
    -- multipart/alternative
    --     text/plain
    --     multipart/related
    --         text/html
    --         image/jpeg
    -- 

    -- Convert to XML.
    DECLARE @xmlStr nvarchar(4000)
    EXEC sp_OAMethod @mime, 'GetXml', @xmlStr OUT

    -- Load into a Chilkat XML object so we can emit in indented format.
    -- (and also to demonstrate how to access the MIME content from XML).
    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @xmlStr
    -- The XML emitted by this statement is shown below, following the remainder of sample code.
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- To get the top-level Subject header field:

    EXEC sp_OAMethod @xml, 'ChilkatPath', @sTmp0 OUT, 'header|subject|*'
    PRINT 'Subject = ' + @sTmp0

    -- Another way of getting the Subject:
    EXEC sp_OAMethod @xml, 'FirstChild2', @success OUT

    EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'subject'
    PRINT 'Subject = ' + @sTmp0
    EXEC sp_OAMethod @xml, 'GetRoot2', NULL

    -- Using the Chilkat XML API, there are many different ways of accomplishing a goal.
    -- This example shows only a few possibilities...

    -- Let's go to the <body> tag containing the HTML.
    -- First we find the XML node where the content-type is text/html.
    -- (XML methods ending in "2" update the internal reference to the XML node.)
    EXEC sp_OAMethod @xml, 'SearchForContent2', @success OUT, @xml, 'content-type', 'text/html'
    IF @success = 1
      BEGIN
        -- From this point, we know the HTML content is two levels up (to the <mime_message> node),
        -- and one level down (to the <body> node).  We can get the HTML content from here:
        DECLARE @htmlContent nvarchar(4000)
        EXEC sp_OAMethod @xml, 'ChilkatPath', @htmlContent OUT, '..|..|body|*'

        PRINT @htmlContent

        PRINT '-'
      END

    -- Back to the root...
    EXEC sp_OAMethod @xml, 'GetRoot2', NULL

    -- How to iterate over all image/jpeg bodies and access the bytes of each?
    EXEC sp_OAMethod @xml, 'SearchForContent2', @success OUT, @xml, 'content-type', 'image/jpeg'
    WHILE @success = 1
      BEGIN
        -- "$" in the Chilkat path indicates to update the internal reference of the caller
        -- if the path could be traversed.
        DECLARE @notUsed nvarchar(4000)
        EXEC sp_OAMethod @xml, 'ChilkatPath', @notUsed OUT, '..|..|body|$'
        EXEC sp_OAGetProperty @xml, 'LastMethodSuccess', @iTmp0 OUT
        IF @iTmp0 = 1
          BEGIN

            EXEC sp_OAMethod @xml, 'GetBinaryContent', @jpgBytes OUT, 0, 0, ''


            PRINT 'Found JPG image!'

            -- Find the next JPG image.
            EXEC sp_OAMethod @xml, 'SearchForContent2', @success OUT, @xml, 'content-type', 'image/jpeg'
          END
        ELSE
          BEGIN
            SELECT @success = 0
          END
      END

    -- --------------------------------------------------------------
    -- --------------------------------------------------------------
    -- The MIME converted to XML:

    -- <?xml version="1.0" encoding="utf-8" ?>
    -- <mime_message>
    --     <header>
    --         <subject>Test email.</subject>
    --         <content-type boundary="------------DB171738719FB06D67DEBAA0">multipart/alternative</content-type>
    --         <mime-version>1.0</mime-version>
    --     </header>
    --     <body>
    --         <subpart>
    --             <mime_message>
    --                 <header>
    --                     <content-type charset="utf-8" format="flowed">text/plain</content-type>
    --                     <content-transfer-encoding>7bit</content-transfer-encoding>
    --                 </header>
    --                 <body><![CDATA[This is a test.
    -- ]]></body>
    --             </mime_message>
    --         </subpart>
    --         <subpart>
    --             <mime_message>
    --                 <header>
    --                     <content-type boundary="------------A940F1230E6F0105F03DB2CB">multipart/related</content-type>
    --                 </header>
    --                 <body>
    --                     <subpart>
    --                         <mime_message>
    --                             <header>
    --                                 <content-type charset="utf-8">text/html</content-type>
    --                                 <content-transfer-encoding>8bit</content-transfer-encoding>
    --                             </header>
    --                             <body><![CDATA[<html><head>
    -- <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    --   </head>
    --   <body bgcolor="#FFFFFF" text="#000000">
    --     <p>This is a test.&nbsp; <img src="cid:part1.E16AE3B4.1505C436@chilkatsoft.com" height="20" width="20"></p>
    --   </body>
    -- </html>
    -- ]]></body>
    --                         </mime_message>
    --                     </subpart>
    --                     <subpart>
    --                         <mime_message>
    --                             <header>
    --                                 <content-type name="starfish20.jpg">image/jpeg</content-type>
    --                                 <content-transfer-encoding>base64</content-transfer-encoding>
    --                                 <content-id>&lt;part1.E16AE3B4.1505C436@chilkatsoft.com&gt;</content-id>
    --                                 <content-disposition filename="starfish20.jpg">inline</content-disposition>
    --                             </header>
    --                             <body>/9j/4AAQSkZJRgABAQEASABIAAD//gAmRmlsZSB3cml0dGVuIGJ5IEFkb2JlIFBob3Rvc2hvcD8g
    -- NC4w/9sAQwAQCwwODAoQDg0OEhEQExgoGhgWFhgxIyUdKDozPTw5Mzg3QEhcTkBEV0U3OFBtUVdf
    -- YmdoZz5NcXlwZHhcZWdj/9sAQwEREhIYFRgvGhovY0I4QmNjY2NjY2NjY2NjY2NjY2NjY2NjY2Nj
    -- Y2NjY2NjY2NjY2NjY2NjY2NjY2NjY2NjY2Nj/8IAEQgAFAAUAwERAAIRAQMRAf/EABcAAAMBAAAA
    -- AAAAAAAAAAAAAAIDBAX/xAAYAQADAQEAAAAAAAAAAAAAAAABAgMEAP/aAAwDAQACEAMQAAAB2kZY
    -- NNEijWKddfTmLgALWH//xAAbEAACAgMBAAAAAAAAAAAAAAABAgMRAAQSE//aAAgBAQABBQL0XqN+
    -- pM2aqJGMiqFFCyg7z//EABwRAAICAgMAAAAAAAAAAAAAAAERAAIQIQMSUf/aAAgBAwEBPwHqU5aq
    -- Axx+y1tMQl4elj//xAAcEQEAAQUBAQAAAAAAAAAAAAABEQACEBIhA1H/2gAIAQIBAT8B3Bhqy7Zc
    -- enyiwmGgDhiOzj//xAAdEAABAwUBAAAAAAAAAAAAAAABAAIREBIhIkFR/9oACAEBAAY/ArZyn+Cg
    -- xtxWuJaoCnqDuin/xAAcEAABBAMBAAAAAAAAAAAAAAABABEhYRAxQVH/2gAIAQEAAT8hkEwPUUR9
    -- DYfE4nxtRpIkBTsayuALIiuY/9oADAMBAAIAAwAAABDWPTsf/8QAGhEAAwADAQAAAAAAAAAAAAAA
    -- AAEREDFBIf/aAAgBAwEBPxC0DVPcWm+Ce4OesrkE6bjH/8QAGBEBAQEBAQAAAAAAAAAAAAAAAREA
    -- QRD/2gAIAQIBAT8QahMiOc8YgSrnTY3ELclHXn//xAAcEAEBAAIDAQEAAAAAAAAAAAABEQAhMUFx
    -- EFH/2gAIAQEAAT8Qn3igmSZSj+c4N4zapMy9IjFV98wncN2iuLFsCEbDGxQkI6RO/n//2Q==</body>
    --                         </mime_message>
    --                     </subpart>
    --                 </body>
    --             </mime_message>
    --         </subpart>
    --     </body>
    -- </mime_message>
    -- 

    -- --------------------------------------------------------------
    -- --------------------------------------------------------------
    -- The MIME sample file loaded at the beginning of this example
    -- contains the following MIME:

    -- Subject: Test email.
    -- Content-Type: multipart/alternative;
    -- 	boundary="------------DB171738719FB06D67DEBAA0"
    -- MIME-Version: 1.0
    -- 
    -- --------------DB171738719FB06D67DEBAA0
    -- Content-Type: text/plain; charset="utf-8"; format=flowed
    -- Content-Transfer-Encoding: 7bit
    -- 
    -- This is a test.
    -- 
    -- --------------DB171738719FB06D67DEBAA0
    -- Content-Type: multipart/related;
    -- 	boundary="------------A940F1230E6F0105F03DB2CB"
    -- 
    -- --------------A940F1230E6F0105F03DB2CB
    -- Content-Type: text/html; charset="utf-8"
    -- Content-Transfer-Encoding: 8bit
    -- 
    -- <html><head>
    -- <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    --   </head>
    --   <body bgcolor="#FFFFFF" text="#000000">
    --     <p>This is a test.&nbsp; <img src="cid:part1.E16AE3B4.1505C436@chilkatsoft.com" height="20" width="20"></p>
    --   </body>
    -- </html>
    -- 
    -- --------------A940F1230E6F0105F03DB2CB
    -- Content-Type: image/jpeg; name="starfish20.jpg"
    -- Content-Transfer-Encoding: base64
    -- Content-ID: <part1.E16AE3B4.1505C436@chilkatsoft.com>
    -- Content-Disposition: inline; filename="starfish20.jpg"
    -- 
    -- /9j/4AAQSkZJRgABAQEASABIAAD//gAmRmlsZSB3cml0dGVuIGJ5IEFkb2JlIFBob3Rvc2hvcD8g
    -- NC4w/9sAQwAQCwwODAoQDg0OEhEQExgoGhgWFhgxIyUdKDozPTw5Mzg3QEhcTkBEV0U3OFBtUVdf
    -- YmdoZz5NcXlwZHhcZWdj/9sAQwEREhIYFRgvGhovY0I4QmNjY2NjY2NjY2NjY2NjY2NjY2NjY2Nj
    -- Y2NjY2NjY2NjY2NjY2NjY2NjY2NjY2NjY2Nj/8IAEQgAFAAUAwERAAIRAQMRAf/EABcAAAMBAAAA
    -- AAAAAAAAAAAAAAIDBAX/xAAYAQADAQEAAAAAAAAAAAAAAAABAgMEAP/aAAwDAQACEAMQAAAB2kZY
    -- NNEijWKddfTmLgALWH//xAAbEAACAgMBAAAAAAAAAAAAAAABAgMRAAQSE//aAAgBAQABBQL0XqN+
    -- pM2aqJGMiqFFCyg7z//EABwRAAICAgMAAAAAAAAAAAAAAAERAAIQIQMSUf/aAAgBAwEBPwHqU5aq
    -- Axx+y1tMQl4elj//xAAcEQEAAQUBAQAAAAAAAAAAAAABEQACEBIhA1H/2gAIAQIBAT8B3Bhqy7Zc
    -- enyiwmGgDhiOzj//xAAdEAABAwUBAAAAAAAAAAAAAAABAAIREBIhIkFR/9oACAEBAAY/ArZyn+Cg
    -- xtxWuJaoCnqDuin/xAAcEAABBAMBAAAAAAAAAAAAAAABABEhYRAxQVH/2gAIAQEAAT8hkEwPUUR9
    -- DYfE4nxtRpIkBTsayuALIiuY/9oADAMBAAIAAwAAABDWPTsf/8QAGhEAAwADAQAAAAAAAAAAAAAA
    -- AAEREDFBIf/aAAgBAwEBPxC0DVPcWm+Ce4OesrkE6bjH/8QAGBEBAQEBAQAAAAAAAAAAAAAAAREA
    -- QRD/2gAIAQIBAT8QahMiOc8YgSrnTY3ELclHXn//xAAcEAEBAAIDAQEAAAAAAAAAAAABEQAhMUFx
    -- EFH/2gAIAQEAAT8Qn3igmSZSj+c4N4zapMy9IjFV98wncN2iuLFsCEbDGxQkI6RO/n//2Q==
    -- 
    -- --------------A940F1230E6F0105F03DB2CB--
    -- 
    -- --------------DB171738719FB06D67DEBAA0--
    -- 

    EXEC @hr = sp_OADestroy @mime
    EXEC @hr = sp_OADestroy @xml


END
GO