SQL Server

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

See more Zip Examples

Example code to unzip Files Matching a Pattern (such as *.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
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  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
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

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