ZipExtract_To_Table

Returns the extracted content a Zip Archive as a table

Stored Procedure
SQL Server Compatibility: 2008, 2012, 2014, 2016, 2017

Syntax:

EXEC SQLHTTP.net.ZipExtract_To_Table @ZipFilePath, @IncludeFilesContent

Arguments:

Name	Type	Description
@ZipFilePath	varchar(4000)	Full path to an existing zip file
@IncludeFilesContent	bit	An indicator as to whether the content of archived files will be included in a column named “Base64Content”



Table Returned:

Column name	Data type	Description
RowID	int	Row Index
Path	nvarchar(4000)	Relative path
FileName	nvarchar(4000)	File name, empty string for directories
IsDirectory	bit	1 = true, 0 = false
Attributes	nvarchar(4000)	Directory, Archive
CompressedSize	bigint	Size in bytes
UncompressedSize	bigint	Size in bytes
Comment	nvarchar(4000)	File comment, if any
CreationTime	datetime
ModifiedTime	datetime
AccessedTime	datetime
CompressionLevel	nvarchar(4000)
CompressionMethod	nvarchar(4000)
CompressionRatio	float	Percentage
CRC	int
Encryption	nvarchar(4000)
UsesEncryption	bit	1 = true, 0 = false
Base64Content	nvarchar(MAX)	If @IncludeFilesContent = 1 this field will contain the content of file in Base64 encoding



Remarks:

Accessing the file system will be performed in the context of the SQL Server instance process. You can set SQL Server to run using a specific network account, or a Local System Account.



Sample Usage:


SET NOCOUNT ON

DECLARE @ZipArchive TABLE(	RowID int, 
				[Path] nvarchar(4000), 
				[FileName] nvarchar(4000), 
				IsDirectory bit, 
				Attributes nvarchar(4000), 
				CompressedSize bigint, UncompressedSize bigint, 
				Comment nvarchar(4000), 
				CreationTime datetime, ModifiedTime datetime, AccessedTime datetime, 
				CompressionLevel nvarchar(4000), 
				CompressionMethod nvarchar(4000), 
				CompressionRatio float, 
				CRC int, [Encryption] nvarchar(4000), UsesEncryption bit, 
				Base64Content nvarchar(MAX))

INSERT INTO @ZipArchive(RowID, 
			[Path], 
			[FileName], 
			IsDirectory, 
			Attributes, 
			CompressedSize, UncompressedSize, 
			Comment, 
			CreationTime, ModifiedTime, AccessedTime, 
			CompressionLevel, 
			CompressionMethod, 
			CompressionRatio, 
			CRC, [Encryption], UsesEncryption, 
			Base64Content)
EXEC SQLHTTP.net.ZipExtract_To_Table 	@ZipFilePath = 'C:\ThisFolderExists\ZipFile.zip',
					@IncludeFilesContent = 1

SELECT RowID, [Path], IsDirectory, CompressionLevel, CompressionRatio, Base64Content
FROM @ZipArchive

SET NOCOUNT ON

DECLARE @ZipArchive TABLE( RowID int,

[Path] nvarchar(4000),

[FileName] nvarchar(4000),

IsDirectory bit,

Attributes nvarchar(4000),

CompressedSize bigint, UncompressedSize bigint,

Comment nvarchar(4000),

CreationTime datetime, ModifiedTime datetime, AccessedTime datetime,

CompressionLevel nvarchar(4000),

CompressionMethod nvarchar(4000),

CompressionRatio float,

CRC int, [Encryption] nvarchar(4000), UsesEncryption bit,

Base64Content nvarchar(MAX))

INSERT INTO @ZipArchive(RowID,

[Path],

[FileName],

IsDirectory,

Attributes,

CompressedSize, UncompressedSize,

Comment,

CreationTime, ModifiedTime, AccessedTime,

CompressionLevel,

CompressionMethod,

CompressionRatio,

CRC, [Encryption], UsesEncryption,

Base64Content)

EXEC SQLHTTP.net.ZipExtract_To_Table @ZipFilePath = 'C:\ThisFolderExists\ZipFile.zip',

@IncludeFilesContent = 1

SELECT RowID, [Path], IsDirectory, CompressionLevel, CompressionRatio, Base64Content

FROM @ZipArchive

RowID Path IsDirectory CompressionLevel CompressionRatio Base64Content

------- --------------------------------- -------------- ------------------ ------------------ ------------------

1 MyExcelWorkbook.xls 0 Default 79.5729417067308 0M8R4KGxGuEAAAA...

2 NewFileName.txt 0 Default 73.8095238095238 TWljcm9zb2Z0IE9...

3 AnotherZipArchive.zip 0 Default 17.479674796748 UEsDBBQAAAAIADA...

4 NestedSubfolder/ 1 Default 0 NULL

5 NestedSubfolder/WordDoc.docx 0 Default 66.8538427557896 BQcm92aWRlciBmb...



ZipExtract_To_Table

Featured API Wrapper Code / SQL Server