---
title: "Azure Data Factory & XML: Loading unsupported file types"
description: Recently I was working with a client to build an Azure analytics solution, replacing an existing on-premises SQL Server implementation.  Here's how I overcame the challenge of moving data from XML files to Azure SQL.
image: https://blog.coeo.com/hubfs/lucas-van-oort-bv6svakgbGM-unsplash.png
---

[![](https://www.coeo.com/wp-content/themes/coeo/images/logo.svg)](https://blog.coeo.com/)

+44 (0)20 3051 3595 | [info@coeo.com](mailto:info@coeo.com) | [Client portal login](https://my.coeo.com)

# Azure Data Factory & XML: Loading unsupported file types

# The Coeo Blog

![Sam Boot](https://blog.coeo.com/hubfs/SamCircleLow.jpg)

Recently I was working with a client to build an Azure analytics solution, replacing an existing on-premises SQL Server implementation.  The scope of the project was to transform and model data from XML files and present the output through Power BI.

The solution designed was:

- Azure Data Lake Storage (ADLS) stores the XML files
- Azure SQL Database stores the transformed data, to which Power BI connects
- Azure Data Factory (ADF) orchestrates the extract, transform and load (ETL) process

## The Challenge

The challenge that presented itself was moving the data from the XML files to the Azure SQL.  ADF does not support XML as a file type, yet.  (Read on for some exciting news from Microsoft about supported file types).  Adding to the complexity was the fact that XML files were potentially 200MB and contained nested structures and arrays up to 8 levels deep.

We explored using the following approaches to overcome this challenge:

- Logic Apps
- Azure Batch
- Azure Databricks

A Logic App could convert the XML into a supported file type such as JSON.  However, the complex structure of the files meant that ADF could not process the JSON file correctly.

Either Azure Batch or Azure Databricks could have been used to create routines that transform the XML data, and both are executable via ADF activities.  Our client had a requirement to manage and modify the solution independently.  Therefore we decided against introducing extra Azure services instead using a service that was already part of the solution and aligned to their existing skill sets.

## Our Solution

Azure SQL Database provides both support for manipulating semi-structured data such as XML, and the ability to connect to external data sources such as files.  Using a combination of these features we were able to load the data from the XML files into an Azure SQL Database.  By using stored procedures, the relevant code could be executed directly from ADF.

Firstly, in Azure SQL Database, we created an external data source to ADLS. We used the [OPENROWSET](https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver15) command in T-SQL to connect to individual XML files via the external data source and insert the data into a staging table as XML.  Finally, with the data staged, the XML can be transformed using nodes() method.

The steps we followed to achieve this were:

1. Create a [database master key](https://docs.microsoft.com/en-gb/sql/t-sql/statements/create-master-key-transact-sql?view=sql-server-ver15).

SQL Server uses database master keys for the management of other keys.  If your database already has a master key, skip this step.

```
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '&MyStr0ngP@$$w0rd';
```

1. Create a [database scoped credential](https://docs.microsoft.com/en-gb/sql/t-sql/statements/create-database-scoped-credential-transact-sql?view=sql-server-ver15).

A database scoped credential is the credential that the database uses to access external locations, in our case ADLS.  When accessing blob storage accounts, a shared access signature (SAS) token, for the storage account, must be generated and used as the identity.

```
CREATE DATABASE SCOPED CREDENTIAL ExampleCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'SAS Token with leading ? removed here';
```

1. Create an [external data source](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15).

Using the previously created credential, we connect to the ADLS putting using the ADLS endpoint URL into the location parameter.

```
CREATE EXTERNAL DATA SOURCE ExampleDataSource
    WITH (
        TYPE = BLOB_STORAGE,
        LOCATION = 'https://MyDataLake.blob.core.windows.net',
        CREDENTIAL = ExampleCredential
    );
```

1. Use the external data source to bulk load XML data into a staging table.

The external data source created in the previous step points to the root directory of the ADLS.  Therefore, we must provide the full path of the data file to the BULK argument.

```
CREATE TABLE dbo.StagingTable 
(
	XMLData XML NOT NULL
);

INSERT INTO dbo.StagingTable
(XMLData)
SELECT BulkColumn
FROM OPENROWSET (
	BULK 'MyContainer/MyFolderPath/MyXMLFile.xml'
	,SINGLE_BLOB
	,DATA_SOURCE = 'ExampleDataSource'
) AS DataFile;
```

1. Once loaded into a table T-SQL is used to query and manipulate the XML. A simple select statement will return the XML.  
   ![XML](https://blog.coeo.com/hs-fs/hubfs/XML.png?width=597&name=XML.png)

Using the [OUTER APPLY](https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver15#using-apply) function and [Nodes()](https://docs.microsoft.com/en-us/sql/t-sql/xml/nodes-method-xml-data-type?view=sql-server-ver15) method, we can write more complicated queries such as returning the name of all the food items from the breakfast menu.

```
SELECT f.XMLData.value('(name/text())[1]','varchar(100)') AS FoodName
FROM [dbo].[StagingTable] t
OUTER APPLY t.XMLData.nodes('/breakfast_menu') AS bm(XMLData)
OUTER APPLY bm.XMLData.nodes('food') AS f(XMLData)
```

![XMLResults](https://blog.coeo.com/hs-fs/hubfs/XMLResults.png?width=247&name=XMLResults.png)

Using some dynamic SQL to parameterise the process, enabling multiple files to be loaded, we implemented steps 4 and 5 as stored procedures that could be executed directly from our ADF pipelines.

## Updates from Microsoft

Now, as promised earlier, some exciting news from Microsoft.  Recently on the ADF forums, the idea to include support for XML file types as a data source has been updated to have a status of 'started'.  In the coming months, we will be able to connect to XML files and imported them to Data Lakes or Databases using the copy activity and mapping data flows.  Progress updates, as and when they happen can be found [here](https://feedback.azure.com/forums/270578-data-factory/suggestions/17508058-xml-file-type-in-copy-activity-along-with-xml-sc).

Similarly, find updates for an even more sought-after file type [here](https://feedback.azure.com/forums/270578-data-factory/suggestions/19807720-add-excel-as-source). ADF may soon have a connector to Microsoft Excel! 

Update 18/06/2020:  [Microsoft Excel](https://docs.microsoft.com/en-us/azure/data-factory/format-excel) is now a supported file type for data sets in ADF.    
Update 26/10/2020: As of July 2020 [XML](https://docs.microsoft.com/en-us/azure/data-factory/format-xml) is now a supported file type in ADF

[![Design your future data platform](https://no-cache.hubspot.com/cta/default/3356718/e10846ed-8619-4ab2-9a36-5ef8af253a25.png)](https://cta-redirect.hubspot.com/cta/redirect/3356718/e10846ed-8619-4ab2-9a36-5ef8af253a25)

### Subscribe to Email Updates

## Related posts

---

### [Microsoft Purview's 14 Key Controls](https://blog.coeo.com/data-governance-with-azure-purview-empowering-your-data-governance-with-microsoft-purview-controls-a-comprehensive-guide)

### [Three ways to profile data with Azure Databricks](https://blog.coeo.com/three-ways-to-profile-data-with-azure-databricks)

### [Data Toboggan - Cool Runnings event](https://blog.coeo.com/data-toboggan-cool-runnings-event)

### [The Chief Data Officer taking a seat at the table](https://blog.coeo.com/the-chief-data-officer-taking-a-seat-at-the-table)

![](https://www.coeo.com/wp-content/uploads/2016/12/logo-invert.png)

+44 (0)20 3051 3595 | info@coeo.com

## Contact Us

By clicking submit below, you consent to allow Coeo to store and process the personal information submitted above to provide you the content requested.

You may unsubscribe from these communications at any time. For more information on how to unsubscribe and our commitment to your privacy, please review our **[Privacy Policy](https://www.coeo.com/privacy/)**.

## Upcoming Events

[See all events](https://www.coeo.com/events/)

#### NOW Building, Thames Valley Park Drive, Reading, RG6 1RB

[![](https://www.coeo.com/wp-content/themes/coeo/images/social-glass.png)](https://www.glassdoor.co.uk/Overview/Working-at-Coeo-EI_IE959052.11,15.htm)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-in.png)](https://www.linkedin.com/company/coeo-ltd)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-twitter.png)](https://twitter.com/CoeoLtd)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-fb.png)](https://www.facebook.com/coeoltd/)

![](https://www.coeo.com/wp-content/themes/coeo/images//menu-icon.png)

![](https://www.coeo.com/wp-content/themes/coeo/images//menu-close.png)

![](https://www.coeo.com/wp-content/uploads/2016/12/logo-invert.png)

+44 (0)20 3051 3595 | [info@coeo.com](mailto:info@coeo.com)

- [Solutions](https://www.coeo.com/solutions/)
- [Next Steps](https://www.coeo.com/next-steps/)
- [Dedicated Support](https://www.coeo.com/dedicated-support/)
- [Case studies](https://www.coeo.com/case-studies/)
- [Technologies](https://www.coeo.com/solutions/technologies/)

- [Industries](https://www.coeo.com/industries/)
- [Finance](https://www.coeo.com/industries/finance/)
- [Retail](https://www.coeo.com/industries/retail/)
- [Technology](https://www.coeo.com/industries/technology/)

- [The Team](https://www.coeo.com/people/)
- [Join Us](https://www.coeo.com/careers/)
- [Graduate Programme](https://www.coeo.com/graduate-programme/)

- [About Coeo](https://www.coeo.com/about-coeo/)
- [The Coeo Blog](https://www.coeo.com/blog/)
- [Contact us](https://www.coeo.com/contact-us/)
- [Privacy Notice](https://www.coeo.com/privacy/)
- [Cookie Policy](https://www.coeo.com/privacy#Cookie_Policy)

- [Events](https://www.coeo.com/events)

Sign up to our newsletter ![go arrow](https://www.coeo.com/wp-content/themes/coeo/images/newsletter-go.png)

 Back to top