SQL Server
SQL Server
Convert MIME to XML
See more MIME Examples
Demonstrates how to convert MIME to XML, and how to access the MIME parts and data from XML.Chilkat SQL Server Downloads
-- 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)
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 @mime int
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
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
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. <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><part1.E16AE3B4.1505C436@chilkatsoft.com></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. <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