(SQL Server) Unzip Files Matching a Pattern (such as *.xml)

Example code to unzip Files Matching a Pattern (such as *.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)
    -- This example works with the .zip located at http://www.example-code.com/data/test.zip

    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- First, let's download the .zip
    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @http, 'Download', @success OUT, 'http://www.example-code.com/data/test.zip', 'qa_test/test.zip'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

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

    EXEC sp_OAMethod @zip, 'OpenZip', @success OUT, 'qa_test/test.zip'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- Show what's in the .zip by getting the directory as XML.
    DECLARE @zipContentsXml nvarchar(4000)
    EXEC sp_OAMethod @zip, 'GetDirectoryAsXML', @zipContentsXml OUT
    EXEC sp_OAGetProperty @zip, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END


    PRINT @zipContentsXml

    -- In this case, the contents of our .zip look like this:
    -- <?xml version="1.0" encoding="utf-8" ?>
    -- <zip_contents>
    --     <dir name="test">
    --         <dir name="abc">
    --             <dir name="123">
    --                 <file>ecc_public.pem</file>
    --                 <file>starfish.jpg</file>
    --             </dir>
    --             <file>comodo-certificates.pem</file>
    --             <file>pigs.xml</file>
    --         </dir>
    --         <file>COMODORSACertificationAuthority.crt</file>
    --         <file>hamlet.xml</file>
    --     </dir>
    -- </zip_contents>

    -- ------------------------------------------------------------------------
    -- If we wish to unzip only .xml files, use "*.xml" for the pattern\
    -- Unzip to the "qa_output" directory relative to our current working directory.
    -- (You may unzip to an absolute path by passing an absolute path, such as "/someDir" or
    -- "C:/someDir", etc.)
    -- This will unzip only the .xml files.  The relative directory structure within
    -- the .zip will be created in the local filesystem under the unzip directory
    -- path passed in the first argument, which in this case is "qa_output".
    DECLARE @count int
    EXEC sp_OAMethod @zip, 'UnzipMatching', @count OUT, 'qa_output', '*.xml', 1
    IF @count < 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END


    PRINT 'unzipped ' + @count + ' matching file(s).'

    -- ------------------------------------------------------------------------
    -- If we wish to unzip only files under the "123" directory, we can do this:
    EXEC sp_OAMethod @zip, 'UnzipMatching', @count OUT, 'qa_output2', '*/123/*', 1
    IF @count < 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END


    PRINT 'unzipped ' + @count + ' matching file(s).'

    -- ------------------------------------------------------------------------
    -- If no asterisk character is used, then the match must be exact.
    -- For example, to unzip "test/hamlet.xml" we must pass the exact path
    -- as it is in the .zip.  We cannot just pass "hamlet.xml".
    EXEC sp_OAMethod @zip, 'UnzipMatching', @count OUT, 'qa_output3', 'test/hamlet.xml', 1
    IF @count < 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END


    PRINT 'unzipped ' + @count + ' matching file(s).'

    -- ------------------------------------------------------------------------
    -- If we want to unzip any file named "hamlet.xml" found in any sub-directory...
    EXEC sp_OAMethod @zip, 'UnzipMatching', @count OUT, 'qa_output4', '*/hamlet.xml', 1
    IF @count < 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END


    PRINT 'unzipped ' + @count + ' matching file(s).'

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @zip


END
GO