(SQL Server) Insert PDF as Base64 into XML, then Extract back to PDF File

Demonstrates how to insert any file into XML using base64 encoding, and then extract back to the original file. This example embeds a PDF in the XML, but the type of file does not matter. It can be any type of file.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- Load our PDF file.
    DECLARE @bd int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @bd, 'LoadFile', @success OUT, 'qa_data/helloWorld.pdf'
    IF @success <> 1
      BEGIN

        PRINT 'Failed to load PDF file.'
        EXEC @hr = sp_OADestroy @bd
        RETURN
      END

    -- Load the following XML:
    -- 
    -- <?xml version="1.0" encoding="utf-8" ?>
    -- <something>
    --     <xyz>
    --         <abc123>A base64 encoded PDF file will be inserted under this node.</abc123>
    --     </xyz>
    -- </something>

    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, 'LoadXmlFile', @success OUT, 'qa_data/xml/xmlToContainPdf.xml'
    IF @success <> 1
      BEGIN

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

    -- Insert the PDF into the XML.
    EXEC sp_OAMethod @bd, 'GetEncoded', @sTmp0 OUT, 'base64'
    EXEC sp_OAMethod @xml, 'NewChild2', NULL, 'xyz|pdfData', @sTmp0

    -- Show the new XML:
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- The XML now looks like this:
    -- <?xml version="1.0" encoding="utf-8" ?>
    -- <something>
    --     <xyz>
    --         <abc123>A base64 encoded PDF file will be inserted under this node.</abc123>
    --         <pdfData>JVBERi0xL ... UlRU9GCg==</pdfData>
    --     </xyz>
    -- </something>

    -- To extract the PDF data out and restore the PDF file:
    DECLARE @bd2 int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd2 OUT

    EXEC sp_OAMethod @xml, 'GetChildContent', @sTmp0 OUT, 'xyz|pdfData'
    EXEC sp_OAMethod @bd2, 'AppendEncoded', @success OUT, @sTmp0, 'base64'
    EXEC sp_OAMethod @bd2, 'WriteFile', @success OUT, 'qa_output/helloWorld.pdf'


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @bd
    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @bd2


END
GO