(SQL Server) Unzip an Entire ZIP Archive While Preserving Directory Structure

See more Zip Examples

Extract an Entire ZIP Archive While Preserving Directory Structure

This example demonstrates how to use the Unzip method to extract all files and directories from a ZIP archive.

The example shows how the stored ZIP directory structure is recreated beneath the target extraction directory.

Suppose the ZIP archive contains:

aaa/pigs.json bbb/base64Cert.txt bbb/sub1/brasil_cert.pem bbb/sub2/penguins.gif bbb/sub2/starfish.jpg hamlet.xml hello.pdf

After extraction to:

c:/temp/testDir

The following filesystem structure will be created:

c:/temp/testDir/aaa/pigs.json c:/temp/testDir/bbb/base64Cert.txt c:/temp/testDir/bbb/sub1/brasil_cert.pem c:/temp/testDir/bbb/sub2/penguins.gif c:/temp/testDir/bbb/sub2/starfish.jpg c:/temp/testDir/hamlet.xml c:/temp/testDir/hello.pdf

If the target extraction directory does not already exist, it is automatically created.

The Unzip method returns the number of files extracted, or -1 if the extraction fails.

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)
    DECLARE @success int
    SELECT @success = 0

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

    -- Open an existing ZIP archive.
    EXEC sp_OAMethod @zip, 'OpenZip', @success OUT, 'qa_data/zips/sample.zip'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- ------------------------------------------------------------
    -- Extract all files to:
    -- 
    --     c:/temp/testDir
    -- 
    -- If the target directory does not already exist,
    -- Chilkat will automatically create it.
    -- 
    -- The Unzip method recreates the stored ZIP directory
    -- structure beneath the target directory.
    -- 
    DECLARE @numFilesUnzipped int
    EXEC sp_OAMethod @zip, 'Unzip', @numFilesUnzipped OUT, 'c:/temp/testDir'

    IF @numFilesUnzipped < 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END


    PRINT 'Number of files extracted = ' + @numFilesUnzipped

    PRINT ''

    -- ------------------------------------------------------------
    -- Suppose the ZIP archive contains these entries:
    -- 
    --     aaa/pigs.json
    --     bbb/base64Cert.txt
    --     bbb/sub1/brasil_cert.pem
    --     bbb/sub2/penguins.gif
    --     bbb/sub2/starfish.jpg
    --     hamlet.xml
    --     hello.pdf
    -- 
    -- After extraction, the following files will exist:
    -- 
    --     c:/temp/testDir/aaa/pigs.json
    --     c:/temp/testDir/bbb/base64Cert.txt
    --     c:/temp/testDir/bbb/sub1/brasil_cert.pem
    --     c:/temp/testDir/bbb/sub2/penguins.gif
    --     c:/temp/testDir/bbb/sub2/starfish.jpg
    --     c:/temp/testDir/hamlet.xml
    --     c:/temp/testDir/hello.pdf
    -- 
    -- The ZIP directory structure is preserved during extraction.

    -- Close the ZIP archive.
    EXEC sp_OAMethod @zip, 'CloseZip', NULL


    PRINT 'Unzip completed successfully.'

    EXEC @hr = sp_OADestroy @zip


END
GO