SQL Server

Extract and Save PDF from SOAP XML

See more XML Examples

Demonstrates how to extract and save PDF file that is embedded in SOAP XML (or any XML). SQL Server

-- 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

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

    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