(SQL Server) Extract Metadata XML from PDF

See more PDF Signatures Examples

Demonstrates how to extract the metadata XML from a PDF.

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)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @pdf int
    -- Use "Chilkat_9_5_0.Pdf" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Pdf', @pdf OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @pdf, 'LoadFile', @success OUT, 'qa_data/pdf/blank_with_metadata.pdf'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        RETURN
      END

    DECLARE @sbXml int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT

    -- Note: Not all PDF files have metadata.  Metadata is optional.
    EXEC sp_OAMethod @pdf, 'GetMetadata', @success OUT, @sbXml
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END

    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, 'LoadSb', @success OUT, @sbXml, 1

    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- Sample PDF metadata XML:
    -- (Code for parsing follows)

    -- Use this online tool to generate parsing code from sample XML: 
    -- Generate Parsing Code from XML

    -- <?xml version="1.0" encoding="utf-8"?>
    -- <x:xmpmeta xmlns:x="adobe:ns:meta/" x:xmptk="Adobe XMP Core 9.1-c001 79.675d0f7, 2023/06/11-19:21:16        ">
    --     <rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#">
    --         <rdf:Description rdf:about="" xmlns:xmp="http://ns.adobe.com/xap/1.0/" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xmpMM="http://ns.adobe.com/xap/1.0/mm/" xmlns:pdf="http://ns.adobe.com/pdf/1.3/" xmlns:xmpRights="http://ns.adobe.com/xap/1.0/rights/">
    --             <xmp:ModifyDate>2024-11-27T11:33:12-06:00</xmp:ModifyDate>
    --             <xmp:CreateDate>2024-11-27T11:28:23-06:00</xmp:CreateDate>
    --             <xmp:MetadataDate>2024-11-27T11:33:12-06:00</xmp:MetadataDate>
    --             <xmp:CreatorTool>Adobe Acrobat Pro (32-bit) 24.3.20112</xmp:CreatorTool>
    --             <dc:format>application/pdf</dc:format>
    --             <dc:title>
    --                 <rdf:Alt>
    --                     <rdf:li xml:lang="x-default">Blank</rdf:li>
    --                 </rdf:Alt>
    --             </dc:title>
    --             <dc:creator>
    --                 <rdf:Bag>
    --                     <rdf:li>Chilkat Software</rdf:li>
    --                 </rdf:Bag>
    --             </dc:creator>
    --             <dc:description>
    --                 <rdf:Alt>
    --                     <rdf:li xml:lang="x-default">Blank Document</rdf:li>
    --                 </rdf:Alt>
    --             </dc:description>
    --             <dc:subject>
    --                 <rdf:Bag>
    --                     <rdf:li>blank</rdf:li>
    --                     <rdf:li>metadata</rdf:li>
    --                     <rdf:li>document</rdf:li>
    --                 </rdf:Bag>
    --             </dc:subject>
    --             <xmpMM:DocumentID>uuid:34535ffa-b632-43f1-b1fd-80cea6fdc351</xmpMM:DocumentID>
    --             <xmpMM:InstanceID>uuid:69ace620-4c54-407f-8d45-6eebc90f34c2</xmpMM:InstanceID>
    --             <pdf:Producer>Adobe Acrobat Pro (32-bit) 24.3.20112</pdf:Producer>
    --             <pdf:Keywords>blank; metadata; document</pdf:Keywords>
    --             <xmpRights:WebStatement>https://www.chilkatsoft.com/</xmpRights:WebStatement>
    --         </rdf:Description>
    --     </rdf:RDF>
    -- </x:xmpmeta>

    DECLARE @x_xmpmeta_xmlns_x nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetAttrValue', @x_xmpmeta_xmlns_x OUT, 'xmlns:x'
    DECLARE @x_xmpmeta_x_xmptk nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetAttrValue', @x_xmpmeta_x_xmptk OUT, 'x:xmptk'
    DECLARE @rdf_RDF_xmlns_rdf nvarchar(4000)
    EXEC sp_OAMethod @xml, 'ChilkatPath', @rdf_RDF_xmlns_rdf OUT, 'rdf:RDF|(xmlns:rdf)'
    DECLARE @rdf_Description_rdf_about nvarchar(4000)
    EXEC sp_OAMethod @xml, 'ChilkatPath', @rdf_Description_rdf_about OUT, 'rdf:RDF|rdf:Description|(rdf:about)'
    DECLARE @rdf_Description_xmlns_xmp nvarchar(4000)
    EXEC sp_OAMethod @xml, 'ChilkatPath', @rdf_Description_xmlns_xmp OUT, 'rdf:RDF|rdf:Description|(xmlns:xmp)'
    DECLARE @rdf_Description_xmlns_dc nvarchar(4000)
    EXEC sp_OAMethod @xml, 'ChilkatPath', @rdf_Description_xmlns_dc OUT, 'rdf:RDF|rdf:Description|(xmlns:dc)'
    DECLARE @rdf_Description_xmlns_xmpMM nvarchar(4000)
    EXEC sp_OAMethod @xml, 'ChilkatPath', @rdf_Description_xmlns_xmpMM OUT, 'rdf:RDF|rdf:Description|(xmlns:xmpMM)'
    DECLARE @rdf_Description_xmlns_pdf nvarchar(4000)
    EXEC sp_OAMethod @xml, 'ChilkatPath', @rdf_Description_xmlns_pdf OUT, 'rdf:RDF|rdf:Description|(xmlns:pdf)'
    DECLARE @rdf_Description_xmlns_xmpRights nvarchar(4000)
    EXEC sp_OAMethod @xml, 'ChilkatPath', @rdf_Description_xmlns_xmpRights OUT, 'rdf:RDF|rdf:Description|(xmlns:xmpRights)'
    DECLARE @xmp_ModifyDate nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @xmp_ModifyDate OUT, 'rdf:RDF|rdf:Description|xmp:ModifyDate'
    DECLARE @xmp_CreateDate nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @xmp_CreateDate OUT, 'rdf:RDF|rdf:Description|xmp:CreateDate'
    DECLARE @xmp_MetadataDate nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @xmp_MetadataDate OUT, 'rdf:RDF|rdf:Description|xmp:MetadataDate'
    DECLARE @xmp_CreatorTool nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @xmp_CreatorTool OUT, 'rdf:RDF|rdf:Description|xmp:CreatorTool'
    DECLARE @dc_format nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @dc_format OUT, 'rdf:RDF|rdf:Description|dc:format'
    DECLARE @rdf_li_xml_lang nvarchar(4000)
    EXEC sp_OAMethod @xml, 'ChilkatPath', @rdf_li_xml_lang OUT, 'rdf:RDF|rdf:Description|dc:title|rdf:Alt|rdf:li|(xml:lang)'
    DECLARE @rdf_li nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @rdf_li OUT, 'rdf:RDF|rdf:Description|dc:title|rdf:Alt|rdf:li'
    EXEC sp_OAMethod @xml, 'GetChildContent', @rdf_li OUT, 'rdf:RDF|rdf:Description|dc:creator|rdf:Bag|rdf:li'
    EXEC sp_OAMethod @xml, 'ChilkatPath', @rdf_li_xml_lang OUT, 'rdf:RDF|rdf:Description|dc:description|rdf:Alt|rdf:li|(xml:lang)'
    EXEC sp_OAMethod @xml, 'GetChildContent', @rdf_li OUT, 'rdf:RDF|rdf:Description|dc:description|rdf:Alt|rdf:li'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @xml, 'NumChildrenHavingTag', @count_i OUT, 'rdf:RDF|rdf:Description|dc:subject|rdf:Bag|rdf:li'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @xml, 'I', @i
        EXEC sp_OAMethod @xml, 'GetChildContent', @rdf_li OUT, 'rdf:RDF|rdf:Description|dc:subject|rdf:Bag|rdf:li[i]'
        SELECT @i = @i + 1
      END
    DECLARE @xmpMM_DocumentID nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @xmpMM_DocumentID OUT, 'rdf:RDF|rdf:Description|xmpMM:DocumentID'
    DECLARE @xmpMM_InstanceID nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @xmpMM_InstanceID OUT, 'rdf:RDF|rdf:Description|xmpMM:InstanceID'
    DECLARE @pdf_Producer nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @pdf_Producer OUT, 'rdf:RDF|rdf:Description|pdf:Producer'
    DECLARE @pdf_Keywords nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @pdf_Keywords OUT, 'rdf:RDF|rdf:Description|pdf:Keywords'
    DECLARE @xmpRights_WebStatement nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @xmpRights_WebStatement OUT, 'rdf:RDF|rdf:Description|xmpRights:WebStatement'

    EXEC @hr = sp_OADestroy @pdf
    EXEC @hr = sp_OADestroy @sbXml
    EXEC @hr = sp_OADestroy @xml


END
GO