Querying XML files with Tableau via Flexter

Uli Bethke Tableau, XML, XSD

Tutorial - Querying XML files with Tableau

Tableau is a great visualisation tool that works with a large number of different file and data formats. One format that is not supported is data in XML files. There are some workarounds, e.g. using Excel to flatten the XML file. However, with deeply nested files or XMLs with a complex schema this approach fails. Excel won’t work either if you have multiple XML files. For those scenarios or if you don’t own a copy of Excel you can use the free online version of Flexter Data Liberator.

In the tutorial below I will walk you through the process of querying XML files with Tableau step by step.

Sample Data - Clinical Studies from clinicaltrials.gov

We will use some sample data from the  www.clinicaltrials.gov  website. Data exchange standards in health care and life sciences are predominantly based on XML. The clinicaltrials.gov website is maintained by the National Library of Medicine (NLM) at the National Institutes of Health (NIH). Information on ClinicalTrials.gov is provided and updated by the sponsor or principal investigator of the clinical study.

Create target schema and CSVs

In a first step we download the XML schema file (XSD)  from the clinicaltrials.gov website. If you would like find out more about the various download options you can visit the Downloading Content for Analysis page.

We then download the XML files about the clinical studies we are interested in, e.g. trials about the Ebola virus.


Once we have downloaded both the XSD and XML files we can process them through Flexter into tab or comma separated files, which is a format that Tableau can consume. Flexter supports many other data formats, e.g. any relational database such as Oracle, SQL Server, MySQL or big data formats such as Parquet.

Let’s first go to the Flexter upload page .

In step one we upload our XSD file.


In step two we upload our XML files. We also register our account so that we can come back to process other XML files



Next we activate our account


Once you have activated your account by following the link in your welcome e-mail you will be able to browse the source schema


Drill into the source schema


Browse the optimized target schema


Drill into the target schema


…and most important of all, download your data for querying in Tableau.

Note: We can see that 21 tables of our target schema were populated with data from our sample trials.


Once you have downloaded the data and extracted it to a folder on the PC where you have Tableau installed you can import it. Flexter automatically creates primary and foreign keys as part of the transformation from XML to TSV. Those keys let you join the data.




We first import the main text file clinical_study.tsv, which sits at the root of our schema. For each output file, Flexter auto-generates a primary key and where relevant a foreign key. In this case it creates the primary key PK clinical study.


We change the data type of the primary key to String to get around a Tableau limitation when dealing with very large numbers.


Next we add two more text files, e.g. collaborator.tsv and authority.tsv. Both data sources contain foreign keys to the clinical_study.tsv file. Both foreign keys were auto-generated by Flexter so that we can join the various output files on common keys.



Once again we change the data type to String


We are now ready to query the data by going to the worksheet


We convert each primary and foreign key to a Tableau Dimension




…and define relationships between our text files


Select Custom and Click the Add… button


We map the primary key of clinical study to the foreign keys in authority and collaborator



Finally we are ready to commence our analysis.


Number of clinical studies by phase


Number of Ebola studies by party type


Which data formats apart from XML also give you the heebie jeebies and need to be liberated? Please leave a comment below or reach out to us.

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.