Accessing files that are stored in BLOB storage during the run-time is a common scenario in most of the implementation. This can be easily achieved when the BLOB container is public faced. In the case of the private containers Shared Access Signature (SAS) facilitate the need of both authenticate and authorization of the file. This documentation from Microsoft explains how the Shared Access Signature works and various use of using Shared Access Signature. Here in my article am going to cover how we can access a XML file in a query that is running in SQL server.
In an On-Premise environment it is easy to access a local file that is stored in the SQL Server. In the cloud world all the artefacts are isolated and are hosted in different environment access to those different artefacts are vital. With Shared Access Signature it makes life easy to achieve the desired result with no pain.
A shared access signature is a signed URI that points to one or more storage resources and includes a token that contains a special set of query parameters. The token indicates how the resources may be accessed by the client.
Microsoft
Generate Shared Access Signature URI
- Login to your azure subscription.
- Navigate to your BLOB container, in the left navigation panel you will find a option called Share Access Signature.
- The Shared Access Signature configuration section will looks like below. Specify your access level that you need for on the various options that are available. There is also a expiry date associated to this, if you need this for a longer period either you can specify a longer range or can automate this Shared Access Signature token creation during your run-time. Once the configuration are selected, click on the Generate Shared Access Signature token button to get the URI.
- Copy the Shared Access Signature URI and remove the leading ? in that URI. A sample URI will look something like this “sv=2019-02-02&ss=bft&srt=sco&sp=rl&se={expiry-date}&st={start-date}&spr=https&sig={token-key}“
Create External Data Source in SQL Server
- Login to the Azure SQL server using the Management Studio.
- Create a master key encryption using the below script.
CREATE MASTER KEY ENCRYPTION BY PASSWORD='{password_to_encrypt}'
- Next create a database scoped credential using the below script.
ALTER DATABASE SCOPED CREDENTIAL {credential_name}
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET ='{SAS_uri_created}'
- Finally create the external data source using the following script. Sample url_of_the_blob_container [‘https://blob-test.blob.core.windows.net/{contianer-name}‘]
CREATE EXTERNAL DATA SOURCE {name_of_the_datasource}
WITH (
TYPE = BLOB_STORAGE,
LOCATION = '{url_of_the_blob_container}',
CREDENTIAL = {name_of_the_credential_created_in_previous_step}
);
SQL Script to access the XML
After created the Shared Access Signature URI and External data source, you can go old school and use OPENROWSET command in SQL to access the XML file. See below for example.
DECLARE @filename VARCHAR(MAX)
DECLARE @DataSourceName VARCHAR(MAX)
DELCARE @xmlfile NVARCHAR(MAX)
DECLARE @xmlData XML
SET @filename = '{filename_of_xml_in_blob}'
SET @filename = '{external_datasource_name_created}'
DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM OPENROWSET (BULK ''' + @filename + ''' , SINGLE_CLOB, DATA_SOURCE = '''+ @DataSourceName +''') AS xmlData';
INSERT @temp EXEC (@sql)
SET @xmlfile = (SELECT * FROM @temp)
SELECT @xmlData = CAST(CAST(@xmlfile AS NTEXT) AS XML)
My Trial 