Storing BLOBs, CLOBs and XML in SQL

In this blog post, I will explore how to use OPENROWSET in SQL to perform operations for BLOBS, CLOBS, external XML files etc.
Storing BLOBs
BLOBs are binary large objects. To store BLOBs we will use the OPENROWSET keyword in SQL. Let's take a look at the SQL first.

declare @BlobTable table(blob_path varchar(150), blob_storage varbinary(max))
insert into @BlobTable (blob_path, blob_storage)
select 'C:\abc.txt' ,
* from openrowset(bulk 'C:\abc.txt', single_blob) as document
select * from @BlobTable

First we create a table to hold the blob object. This column is of type varbinary(max) and then we insert the file text into this column using OPENROWSET. The option single_blob returns the contents as single row, single column rowset of type varbinary(max).

Storing CLOBs
CLOBs are character large objects. To store CLOBs we will again use the OPENROWSET keyword in SQL. The SQL statement looks like -

declare @ClobTable table(clob_path varchar(150), clob_storage varchar(max))
insert into @ClobTable (clob_path, clob_storage)
select 'C:\abc.txt' ,
* from openrowset(bulk 'C:\abc.txt', single_clob) as document
select * from @ClobTable

The only 2 differences between this statement and the one above is that here the clob_storage is defined as varchar(max) and the option provided to OPENROWSET is single_clob. The option single_clob returns the contents as single row, single column rowset of type varchar(max). It reads the file provided in ASCII.

Storing NCLOBs
The only difference between storing CLOB and NCLOB is that the datatype used is nvarchar(max) and the file should be saved with UNICODE encoding. The SQL statement looks like:

declare @NClobTable table(clob_path varchar(150), clob_storage nvarchar(max))
insert into @NClobTable (clob_path, clob_storage)
select 'C:\abcUnicode.txt' ,
* from openrowset(bulk 'C:\abcUnicode.txt', single_nclob) as document
select * from @NClobTable

Inserting/Updating XML from file
MSDN website says that we should import xml data using the single_blob option only, rather than single_clob and single_nclob option as only single_blob option supports all windows encoding conversions. Let's take a look at an example

declare @New_varbinary varbinary(max)
set @New_varbinary = (select * from openrowset(bulk 'C:\abc.xml', single_blob) as document)
update TableWithXmlColumn 
set TheXmlColumn = @New_varbinary
where SomeId = 1653

Difference between varbinary, varchar, nvarchar
nvarchar is used to store UNICODE data while varchar is used to store ASCII data. We should use nvarchar if the database requirements are for multilingual support. This will allow for extended set of characters. If the data stored not need any multilingual support than we should use varchar. The reason being nvarchar uses twice as much space as varchar to support extended character codes. These data types in SQL are categorized in different categories: varbinary belongs to binary strings category. varchar belongs to character strings category and nvarchar belongs to Unicode character strings.