I'm using the Azure SQL Database and need to read an XML file from the Azure Storage Account. I've used openrowset but got the error: "File '2025/02/01/FUL01of01.xml' cannot be opened because it does not exist or it is used by another process."

The file exists in the storage account and I can read any CSV file from the same container with openrowset but I can't read the XML file. The following are the credentials, External data source, and the query:

CREATE DATABASE SCOPED CREDENTIAL [myCredential]
    WITH IDENTITY = N'Managed Identity';

CREATE EXTERNAL DATA SOURCE [myDataSource] WITH (TYPE = BLOB_STORAGE, LOCATION = N'', CREDENTIAL = [myCredential]);

DECLARE @xml XML;

SELECT @xml = BulkColumn 
FROM OPENROWSET(
    BULK '2025/02/01/FUL01of01.xml', 
    DATA_SOURCE = 'myDataSource', 
    SINGLE_BLOB
) AS x;

SELECT @xml;

I'm using the Azure SQL Database and need to read an XML file from the Azure Storage Account. I've used openrowset but got the error: "File '2025/02/01/FUL01of01.xml' cannot be opened because it does not exist or it is used by another process."

The file exists in the storage account and I can read any CSV file from the same container with openrowset but I can't read the XML file. The following are the credentials, External data source, and the query:

CREATE DATABASE SCOPED CREDENTIAL [myCredential]
    WITH IDENTITY = N'Managed Identity';

CREATE EXTERNAL DATA SOURCE [myDataSource] WITH (TYPE = BLOB_STORAGE, LOCATION = N'https://myStorageAccount.blob.core.windows/myContainer', CREDENTIAL = [myCredential]);

DECLARE @xml XML;

SELECT @xml = BulkColumn 
FROM OPENROWSET(
    BULK '2025/02/01/FUL01of01.xml', 
    DATA_SOURCE = 'myDataSource', 
    SINGLE_BLOB
) AS x;

SELECT @xml;

• xml
• azure-sql-database
• openrowset

• Do ylu have proper roles to acess file? – Pratik Lad Commented Feb 5 at 11:31

1 Answer 1

File '' cannot be opened because it does not exist or it is used by another process.

As per the error we can see that you don't have proper permissions to access the file if you are sure that the file doesn't exist in blob storage.

To resolve this issue, you need to have proper rights to access the file. First grant Storage Blob Data Contributor role on the storage account to yourself and managed identity of azure sql.

Check if you have any ACL enabled for other users on the folders containing xml file.

Go to the storage account >> click on container >> clock on your respective folder >> click manage ACL>> then allow read, write, execute permissions to all users.

Here is my output: