Importing Multiple XML Files into Azure SQL Database

For this blog post, our goal is to load multiple XML files into a database so that we can distribute their structure across separate tables. An additional complication is that the XML files have different filenames with each delivery. This is a fairly common scenario, for example, in bank payments. But how do we actually set something like this up in Azure?

Non-Azure architecture

First, let’s compare this to a non-Azure environment, where something like this is generally easy to implement. For example, the XML files are stored in a network location within the company’s own network. A database such as SQL Server runs on the company’s own database server and can retrieve a list of XML files from that network location. SQL Server then iterates through this list and imports all the XML files into a table with a column of data type XML. The only remaining step is to distribute the content across different tables. This distribution does require some explanation, but that is not the purpose of this blog post.

Azure Architecture

This blog post discusses a use case in the Azure environment. The XML files are stored in a folder on Azure BLOB Storage, and the target database is an Azure SQL Database. So it’s not SQL Server, but actually Azure’s SQL Database. Scalability, rapid development, and cost savings are some of the benefits of Azure.

I’m writing this blog because, while importing multiple files into Azure is certainly feasible, in our case you quickly run into limitations imposed by individual components. For example, an alternative approach is needed to determine the list of XML files. To do this, I’ll use Azure Data Factory, which is one of the other Azure services offered by Microsoft. In addition, specific settings are required for the components.

Importing a Single XML File into Azure SQL Database

Let's start with the basics. Importing a single XML file from Azure BLOB Storage into an Azure SQL Database. To do this, we’ll create an External Data Source in Azure SQL Database. An External Data Source can be created for multiple types of sources, including Hadoop, another Azure SQL Database, or BLOB Storage. We’ll use the latter type to import files from Azure BLOB Storage into Azure SQL Database.

There are 5 files in Azure BLOB Storage, and we will now start by reading the file “First_5gh2rfg.xml.” To do this, we’ll create a target table in Azure SQL Database. The contents of the XML file will be stored in a column with a special XML data type. Important => This data type is necessary to easily parse the contents of an XML file and distribute them across multiple tables.

With a simple query in Azure SQL Database, you can retrieve the data from Azure BLOB Storage and load it into the table:

INSERT INTO [BI].[XML_IMPORT] (XML_DATA)
SELECT CAST(BulkColumn AS XML)
FROM OPENROWSET
(
 BULK 'MyStorage/MainFolder/First_5gh2rfg.xml',
 DATA_SOURCE = 'AzureBlobStorage', 
 SINGLE_BLOB
) AS XML_IMPORT

In this example, “MyStorage” is the BLOB container, “MainFolder” is the main folder in the BLOB container, and “First.xml” is one of the XML files in that folder. Reading a single XML file into Azure SQL Database from Azure BLOB Storage is therefore easy to set up.

The Challenge

But what if we want to import multiple files with different names all at once for each shipment? In that case, we initially run into the following issues:

• You cannot use “*.xml” when importing, because a specific filename must be specified
• But the key point is that Azure SQL Database cannot access Azure BLOB Storage to retrieve a list of files

Alternatively, you can use Azure Data Factory. In Azure Data Factory, you can define various sources and create a pipeline with one or more activities. The Copy Data Activity allows you to transfer data from various sources to numerous destinations, including from Azure BLOB Storage to Azure SQL Database. An example of such a pipeline is shown below.

This seems exactly like what we want. However, while this would be a piece of cake for multiple CSV files… unfortunately, it’s not the case for XML files that need to be stored in a database column with the XML data type. The specific XML data type is not (yet) supported, as shown below in the source definition for Azure BLOB Storage.

With the String data type, each line in an XML file would be imported as a separate record, causing the structure to be lost. Distributing the content across multiple tables then becomes a challenge in itself. The column with the XML data type also expects a complete XML file and will return an error message when reading it.

The Solution

Ultimately, the solution is a hybrid approach. We can also use Azure Data Factory to import the list of XML files into the Azure SQL Database. Azure Data Factory then calls upon the Azure SQL Database so that it can iterate through that list itself. This means that multiple XML files can be imported into the Azure SQL Database all at once!

The pipeline looks like this:

A helper table has been created in Azure SQL Database that will contain only file names.

Activity 1 in the pipeline is to clear that table by calling a stored procedure in Azure SQL Database

Activity 2 and Activity 3 require further explanation, because they are the core of the solution for storing the list of file names from Azure BLOB Storage in an Azure SQL Database table.

The second Activity is a “Get Metadata” Activity, for which it is important to select the “Child Items” and “Item Name” arguments. In our case, “Child Items” refers to the filenames in the Azure BLOB Storage folder “MainFolder.” The BLOB Storage dataset is configured as shown in the image below. Under “File path,” the folder is specified, but the “File” field is left blank.

Back to the pipeline to the third Activity of type ForEach

In the ForEach activity, we set “@activity(‘Get Blob File Names’).output.childItems” for Items. This ensures that a specific underlying activity can be executed for each file in Azure Blob Storage.

When we open the ForEach loop, we see a call to a stored procedure there. The stored procedure was created in Azure SQL Database as follows:

CREATE PROCEDURE [BI].[XML_IMPORT_GET_FILE_NAME] (@Name_Of_File varchar(max)) AS
BEGIN
 INSERT INTO BI.BLOB_FILE_NAME SELECT @Name_Of_File
END

In the @Name_Of_File parameter of this stored procedure, we can then pass the XML filename that needs to be made available in the ForEach loop. This is done by setting the parameter value to the text “@item().Name.” NOTE! This is case-sensitive, so “@item().name” will not work, even though Azure Data Factory internally treats the property as “name” (lowercase).

The interim result of the pipeline after Activities 1 through 3 is a populated Azure SQL Database table containing all the file names stored in Azure BLOB Storage!

The fourth and final activity is, once again, calling a stored procedure in Azure SQL Database. This stored procedure will iterate through the table containing the file names and use them to populate the XML column for further categorization.

The beginning of this stored procedure is shown below, where you can see that it uses a simple query to read a single file. That query is, so to speak, constructed each time, with the specific filename retrieved from the table mentioned above using a cursor.

CREATE PROCEDURE [BI].[XML_IMPORT_AND_SPLIT] AS
BEGIN
 DECLARE @name_of_file varchar(8000)
 DECLARE cursor_file_names CURSOR FAST_FORWARD
 FOR SELECT NAME_OF_FILE FROM BI.BLOB_FILE_NAME WHERE NAME_OF_FILE LIKE '%.xml'
 OPEN cursor_file_names
 FETCH NEXT FROM cursor_file_names into @name_of_file
 WHILE @@FETCH_STATUS = 0 
 BEGIN 
 EXEC
 (
 'INSERT INTO [BI].[XML_IMPORT](XML_DATA) 
 SELECT CAST(BulkColumn AS XML)
 FROM OPENROWSET
 (
 BULK ''MyStorage/MainFolder/' + @name_of_file + ''',
 DATA_SOURCE = ''EDS_AzureBlobStorage'', 
 SINGLE_BLOB
 ) as XML_IMPORT'
 )
 FETCH NEXT FROM cursor_file_names into @name_of_file
 END
 DEALLOCATE cursor_file_names

This procedure actually also involves splitting the XML into multiple tables, but that's beyond the scope of this blog.

Conclusion

Importing multiple files from Azure BLOB Storage into Azure SQL Database is a breeze with Azure Data Factory. However, if we want to load XML files into an XML column for further processing, we run into the individual limitations of Azure components. Nevertheless, with a specific approach and the right settings, it can still be handled effectively using Azure Data Factory.