(SQL Server) Extract and Save PDF from SOAP XML

Demonstrates how to extract and save PDF file that is embedded in SOAP XML (or any 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)
    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

    DECLARE @success int
    EXEC sp_OAMethod @xml, 'LoadXmlFile', @success OUT, 'qa_data/soap/soapWithPdf.xml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @xml, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END

    -- The XML we loaded looks like this:

    -- <?xml version="1.0" encoding="utf-8" ?>
    -- <s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
    --     <s:Body>
    --         <ObtenerPDFResponse xmlns="http://xyz.org/">
    --             <ObtenerPDFResult xmlns:a="http://schemas.xyz.org/something" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    --                 <a:CBB i:nil="true" />
    --                 <a:CodigoConfirmacion i:nil="true" />
    --                 <a:ErrorDetallado />
    --                 <a:ErrorGeneral i:nil="true" />
    --                 <a:FechaGenerada i:nil="true" />
    --                 <a:FolioGenerado i:nil="true" />
    --                 <a:OperacionExitosa>true</a:OperacionExitosa>
    --                 <a:PDF>JVBERi0xLjQKJe...</a:PDF>
    --                 <a:XML i:nil="true" />
    --             </ObtenerPDFResult>
    --         </ObtenerPDFResponse>
    --     </s:Body>
    -- </s:Envelope>

    DECLARE @pdfElement int
    EXEC sp_OAMethod @xml, 'FindChild', @pdfElement OUT, 's:Body|ObtenerPDFResponse|ObtenerPDFResult|a:PDF'
    EXEC sp_OAGetProperty @xml, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

        PRINT 'Did not find the PDF element at the expected location.'
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END

    EXEC sp_OAMethod @pdfElement, 'SaveBinaryContent', @success OUT, 'qa_output/out.pdf', 0, 0, ''
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @xml, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END


    PRINT 'Successfully extracted and saved the PDF.'

    EXEC @hr = sp_OADestroy @xml


END
GO