Closure
Closure
Home » Databases » Microsoft SQL Server » How can we store docx, xlsx, jpg, xml, etc. files in a database table? in SQL Server

How can we store docx, xlsx, jpg, xml, etc. files in a database table? in SQL Server

How can we store docx, xlsx, jpg, xml, etc. files in a database table? in SQL Server
, , , , 1 Comment
How can we store docx, xlsx, jpg, xml, etc. files in a database table? in SQL Server
Latest posts by Stratos Matzouranis (see all)

In SQL Server we have the ability to store files such as Docx, Xlsx, XML, JSON, JPG, PNG etc. in a field of a database table. In this article we will analyze how this is done and how it is done the other way around we export these files from the database to a folder in the file system.

How do we import the files from a folder into the table

To begin with, we will have to make the table in which we will insert them. In this table we will define an identity field with sequential numbering, the file name, the file type and finally a type field varbinary(max) which will contain the entire file in binary form.

create table mig_db.dbo.docs (
id int identity(1,1),
docname nvarchar(200),
filetype varchar(100),
document varbinary(max)
);

alter table mig_db.dbo.docs add constraint pk_docs primary key clustered(id);

The files we want to upload are located in the following folder on our server:

How can we store docx, xlsx, jpg, xml, etc. files in a database table? in SQL Server
1

All we have to do now is load in the array with insert one by one the files we want. The field that will contain the file is loaded as blob (binary large object) directly with the known function openrowset, as shown in the following examples:

insert into mig_db.dbo.docs
select N'doc1.docx','.docx',bulkcolumn from openrowset (bulk N'c:\SQL2019\doc1.docx', single_blob) as lob;

insert into mig_db.dbo.docs
select N'doc2.docx','.docx',bulkcolumn from openrowset (bulk N'c:\SQL2019\doc2.docx', single_blob) as lob;

insert into mig_db.dbo.docs
select N'doc3.doc','.doc',bulkcolumn from openrowset (bulk N'c:\SQL2019\doc3.doc', single_blob) as lob;

insert into mig_db.dbo.docs
select N'dp_logo.jpg','.jpg',bulkcolumn from openrowset (bulk N'c:\SQL2019\dp_logo.jpg', single_blob) as lob;

insert into mig_db.dbo.docs
select N'excel1.xlsx','.xlsx',bulkcolumn from openrowset (bulk N'c:\SQL2019\excel1.xlsx', single_blob) as lob;

insert into mig_db.dbo.docs
select N'pdfdoc.pdf','.pdf',bulkcolumn from openrowset (bulk N'c:\SQL2019\pdfdoc.pdf', single_blob) as lob;

After we finish loading, if we select the table we will see these files in binary format:

select * from mig_db.dbo.docs;
How can we store docx, xlsx, jpg, xml, etc. files in a database table? in SQL Server
2

How do we extract the files from the table back to a folder

To export the files we have two ways, we will see first the difficult one and then the easy one.

Hard-way Export (BCP)

The hard way is by using it BCP and his xp_cmdshell. The difficulty is not in using them but in the fact that the files exported by BCP will appear as corrupted as they will need formatting.

The process is as follows:

  • Enable xp_cmdshell if it isn't already
  • With the next query, let's create the template with the format that the files should have (template.fmt).
  • In the file that will be created (template.fmt) let's change where it has 8 with 0.
  • Let's run the next BCP query in which we have defined which file we want from the table, to which file path we want it to be exported and the file with the format we created in the previous step.

--hard way
exec sp_configure 'show advanced options', '1'
RECONFIGURE
exec sp_configure 'xp_cmdshell', '1' 
RECONFIGURE


SET NOCOUNT ON;
CREATE TABLE tt(the_file VARBINARY(MAX) NOT NULL);
GO
SET NOCOUNT ON;
DECLARE @bcp_cmd VARCHAR(8000);
SET @bcp_cmd='BCP '+QUOTENAME(DB_NAME())+'.dbo.tt format nul -T -N -f "C:\SQL2019\export\template.fmt"';
EXEC master.sys.xp_cmdshell @bcp_cmd;
DROP TABLE tt;
GO
-- Anoigoume to file (template.fmt) pou exei dimiourgei kai allazoume tin timi 8 me 0.



SET NOCOUNT ON;
DECLARE @bcp_cmd VARCHAR(8000);
SET @bcp_cmd='BCP "SELECT document FROM mig_db.dbo.docs where docname =''dp_logo.jpg''" QUERYOUT "C:\SQL2019\export\dp_logo.jpg" -T -fC "C:\SQL2019\export\template.fmt"';
EXEC master..xp_cmdshell @bcp_cmd;
GO

Easy-way (Ole Automation Procedures)

I didn't make this script myself, the main part of it came from him Jitendra to which I made a few modifications.

The process with this method is very simple. All we have to do is:

  • Let's enable 'Ole Automation Procedures' if it isn't already.
  • Set the @outputpath variable to the folder where the export will take place.
  • Define the query with the data we want to export (the field with the file name and the field with the varbinary) in the table-variable @Doctable.
  • Finally, let's run the query all together.

--easy way
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO


DECLARE @outPutPath varchar(50) = 'C:\SQL2019\export'   ------------ <-------- Export path
, @i bigint
, @init int
, @data varbinary(max) 
, @fPath varchar(max)  
, @folderPath  varchar(max) 
 
--Get Data into temp Table variable so that we can iterate over it 
DECLARE @Doctable TABLE (id int identity(1,1), [FileName]  varchar(100), [Doc_Content] varBinary(max) )
 
INSERT INTO @Doctable([FileName],[Doc_Content])
select docname,document from dbo.docs;   ------------ <---------- Export query

--SELECT * FROM @table

SELECT @i = COUNT(1) FROM @Doctable
 
WHILE @i >= 1
BEGIN 

	SELECT 
	 @data = [Doc_Content],
	 @fPath = @outPutPath + '\' +[FileName]
	FROM @Doctable WHERE id = @i
 
  
  EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instance created
  EXEC sp_OASetProperty @init, 'Type', 1;  
  EXEC sp_OAMethod @init, 'Open'; -- Calling a method
  EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
  EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
  EXEC sp_OAMethod @init, 'Close'; -- Calling a method
  EXEC sp_OADestroy @init; -- Closed the resources
 
  print 'Document Generated at - '+  @fPath   

--Reset the variables for next use
SELECT @data = NULL  
, @init = NULL
, @fPath = NULL  
SET @i -= 1
END

After it is finished we will see that it has exported to the folder all the files we had in the table:

How can we store docx, xlsx, jpg, xml, etc. files in a database table? in SQL Server
3

Sources:

Share it

1 thought on “Πώς μπορούμε να αποθηκεύουμε μέσα σε πίνακα της βάσης δεδομένων αρχεία docx, xlsx, jpg, xml κ.α. στον SQL Server”

Leave a reply