SQL Server

Extract PDF from General Ledger Transactions XML

See more Office365 Examples

Demonstrates how to extract the base64 data representing a PDF file from a General Ledger Transactions 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

    -- The General Ledger Transactions XML contains the following:

    -- <?xml version="1.0" encoding="utf-8" ?>
    -- <eExact xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="eExact-XML.xsd">
    --     <GLTransactions>
    --         <GLTransaction entry="100000095">
    --             <TransactionType number="20" />
    --             <Journal type="20" code="VK">
    --                 <Description termid="90003">Verkopen</Description>
    --                 <GLAccount type="20" code="400000" />
    --                 <Currency code="EUR" />
    --             </Journal>
    --             <Date>2022-04-05</Date>
    --             <Document>
    --                 <Subject>100000095</Subject>
    --                 <DocumentType number="10" />
    --                 <Account code="0001" status="C" />
    --                 <Amount>
    --                     <Currency code="EUR" />
    --                     <Value>1382.91</Value>
    --                 </Amount>
    --                 <References>
    --                     <InvoiceNumber>100000095</InvoiceNumber>
    --                 </References>
    --                 <Attachments>
    --                     <Attachment>
    --                         <Name>F_100000095.PDF</Name>
    --                         <BinaryData>BASE64_ENCODED_PDF_BYTES_HERE</BinaryData>
    --                     </Attachment>
    --                 </Attachments>
    --             </Document>
    --             <GLTransactionLine type="20" status="20" line="0" linetype="0">
    --                 <Date>2022-04-05</Date>
    --                 <VATType>S</VATType>
    --                 <FinYear number="2022" />
    --                 <FinPeriod number="4" />
    --                 <GLAccount type="20" code="400000" />
    --                 <Description>100000095</Description>
    --                 <DueDate>2022-05-05</DueDate>
    --                 <Account code="0001" status="C">
    --                     <Name>90 Degrees &amp; co</Name>
    --                 </Account>
    --                 <Amount>
    --                     <Currency code="EUR" />
    --                     <Value>1382.91</Value>
    --                 </Amount>
    --                 <ForeignAmount>
    --                     <Currency code="EUR" />
    --                     <Value>1382.91</Value>
    --                     <Rate>1</Rate>
    --                 </ForeignAmount>
    --                 <References>
    --                     <InvoiceNumber>100000095</InvoiceNumber>
    --                 </References>
    --             </GLTransactionLine>
    --             <PaymentTerms>
    --                 <PaymentTerm entry="100000095" type="20" line="0" paymentMethod="B">
    --                     <Description>20</Description>
    --                     <GLAccount type="20" code="400000" />
    --                     <Amount>
    --                         <Currency code="EUR" />
    --                         <Debit>1382.91</Debit>
    --                         <Credit>0</Credit>
    --                     </Amount>
    --                     <ForeignAmount>
    --                         <Currency code="EUR" />
    --                         <Debit>1382.91</Debit>
    --                         <Credit>0</Credit>
    --                     </ForeignAmount>
    --                     <Reference />
    --                     <YourRef />
    --                     <InvoiceNumber>100000095</InvoiceNumber>
    --                     <InvoiceDate>2022-04-05</InvoiceDate>
    --                     <DueDate>2022-05-05</DueDate>
    --                     <PaymentDate>2022-05-05</PaymentDate>
    --                 </PaymentTerm>
    --             </PaymentTerms>
    --         </GLTransaction>
    --     </GLTransactions>
    -- </eExact>

    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, 'c:/temp/example.xml'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @xml, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END

    DECLARE @pdfFilename nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @pdfFilename OUT, 'GLTransactions|GLTransaction|Document|Attachments|Attachment|Name'
    EXEC sp_OAGetProperty @xml, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'No XML element at the given path.'
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END


    PRINT 'PDF Filename = ' + @pdfFilename

    DECLARE @binaryDataElem int
    EXEC sp_OAMethod @xml, 'GetChildWithTag', @binaryDataElem OUT, 'GLTransactions|GLTransaction|Document|Attachments|Attachment|BinaryData'
    EXEC sp_OAGetProperty @xml, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'No XML element at the given path.'
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END

    -- Decode the base64 data and save to a file.
    EXEC sp_OAMethod @binaryDataElem, 'SaveBinaryContent', @success OUT, @pdfFilename, 0, 0, ''
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @binaryDataElem, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
      END
    ELSE
      BEGIN

        PRINT 'Success.'
      END

    EXEC @hr = sp_OADestroy @binaryDataElem


    EXEC @hr = sp_OADestroy @xml


END
GO