XML in Excel

What Is XML in Excel?

Extensible Markup Language (XML) is a widely used data storage format on the web. Generally, users work with XML structure data in Excel in certain circumstances. In simple terms, XML is the form of storing the data in a pre-defined structure that the apps and systems can easily read. However, we need to convert it into a format that can be read easily.

Also, XML is the widely accepted file format for storing and transmitting data on the web.

For example, consider the following sample data of XML data structure of two records.

Record 1: 

<record>

<Mobile Brand> Samsung </Mobile Brand >

<Units Sold> 5000 </Units Sold >

<Make Year> 2009 </Make Year>

</record>

Record 2:

<record>

<Mobile Brand> Apple </Mobile Brand >

<Units Sold> 3000 </Units Sold >

<Make Year> 2009 </Make Year>

</record>

Though the above data structure may look confusing, let us break it down for better understanding.

To begin with, the word <record> is the beginning of the data structure.

Next, the tag <Mobile Brand> is the first header or column of the data table. The first value in this column is ‘Samsung,’ and the same value needs to end with the same tag </Mobile Brand>.

Then, the tag <Units Sold> is the second header or column of the data table. The first value in this column is ‘5000,’ and the same value needs to end with the same tag </Units Sold>.

Similarly, the tag <Make Year> is the third header or column of the data table. The first value in this column is ‘2009’; the same value needs to end with the same tag </Make Year>.

It concludes the data table’s first row, which needs to be ended with the word </record>.

Likewise, the next row of records starts with the word <record> and ends with </record>.

Also, the record will appear like the following table in Excel.

XML in Excel Intro
Key Takeaways
  • We can import data from the XML source file using the Developer tab or the power query feature.
  • XML data is also available in web URLs, and the power query can easily load and transform the data.
  • Creating a connection from a power query makes the connection dynamic. Whenever changes happen in the source XML file, we have to click on the Refresh button of the connection created by the power query, and all the changes will be updated dynamically.
  • Also, XML data stores each information where headers of the data are tagged in brackets on either side of the actual value.

How To Import And Convert XML File To Excel?

We can import XML in Excel in two ways, the first is by using an XML extension file stored in our system, and the second is directly from the web.

Let us have a look at the following examples to understand both the ways.

Import XML File Saved On Your System:

First, we need to save the XML structured file in our system. Before that, let us design the XML file schema.

To begin with, add the following code to the notepad file.

<data-set xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”>

It is the standard set of lines. We need to add it the beginning of the XML data.

XML in Excel Import.1

Once we add the set of lines, copy the following XML structured data to the same notepad file.

<record>

       <FullName>Andy Flower</FullName>

       <Salary>10000</Salary>

       <Country>UK</Country>

       <Age>23</Age>

    </record>

    <record>

       <FullName>Andy Bitchel</FullName>

       <Salary>12000</Salary>

       <Country>USA</Country>

       <Age>25</Age>

    </record>

</data-set>

XML in Excel Import.2

Now, click on the Save option and save the file in the XML format, as shown in the following image.

XML in Excel Import.3

Next, click on the Save button. We can see that the file is saved in XML format.

Import.4

After saving the file, we need to import this in Excel under the Developer tab.

Developer Tab

Though the Developer tab in Excel is unavailable, we can use the following steps to enable it.

Step 1: To begin with, go to the File tab.

File Tab

Step 2: Next, click on Options.

File Options

Step 3: The Excel Options window opens up. Now, click on Customize Ribbon.

Customize Tab2

Step 4: Then, check the Developer tab box under Main Tab.

Developer Tab Options

Step 5: Next, click OK.

Now, we will be able to see the Developer tab.

Developer Tab

Once the Developer tab is enabled, click on the Source option under this tab.

Import.5

Now, the XML Source window opens on the right side of our Excel screen. 

Click on the XML Maps… option in this window.

XML in Excel Import.6

Next, it will open another window, as shown in the following image.

Click on the Add button.

XML in Excel Import.7

Now, we need to choose the XML saved file from our computer. Navigate to the folder and choose the XML file.

Import.4

Click on Open, and Excel will prompt the following message. Click OK.

XML in Excel Import.8

Now, we are back to the previous window.

Next, click OK in this window.

XML in Excel Import.9

Now, we can see all the column names in the selected XML table in the XML window on the right side.

XML in Excel Import.10

Next, select all the columns in the above window. Then, drag and drop the selected columns to the worksheet.

XML in Excel Import.11

Once we drag and drop the columns, Excel will create an empty table.

Now, under the Developer tab, click on the Import option.

Developer tab (Import)

Next, it will ask us to choose the XML file from our saved folders.

Then, choose the same file that we have saved in the subsequent steps.

XML in Excel Import.12

Next, click on Import, and XML data will be stored as shown in the following image:

XML in Excel Import.13

In this way, we can import XML in excel. Next, let us look into the second method.

Import XML File using the Web URL

If the XML data is from the web, then we can import this from the web by using a power query.

For instance, the following URL contains the data in XML format.

https://www.forbes.com/forbes_2008_sitemap.xml

The data will appear like the following image:

Web Url

We will import this by using a power query.

First, go to the Data tab.

Next, click on the drop-down list of Get Data, hover on From Other Sources, and choose From Web.

XML in Excel Web Url.1

Now, we should enter the URL in the From Web window.

https://www.forbes.com/forbes_2008_sitemap.xml
XML in Excel Web Url.2

Then, click OK. Now, Excel will create a connection from the mentioned URL.

Power Query preview data will be displayed as shown in the below image:

Web Url.3

Click on Load, and XML data will be downloaded to the Excel file.

XML in Excel Web Url.4

Import With Power Query:

We can easily import the data using a power query. Under the Data tab, we have a group called Get & Transform Data.

Web Url.5

To begin with, click on Get Data select From File and choose From XML.

XML in Excel Web Url.6

Now, we should choose the file from the desired folder.

Import.4

Next, click on Import. It will display the data preview as shown in the following image. 

Web Url.7

Click on Load.

Now, Excel creates a connection between the XML and Excel files.

Web Url.8

Meanwhile, importing the data through a power query is best because, whenever we make a change, we can get the updated data easily to Excel with just a click of a button.

For instance, add the following record to the existing XML file.

<record>

       <FullName>Andy Robert</FullName>

       <Salary>15000</Salary>

       <Country>USA</Country>

       <Age>30</Age>

    </record>

Web Url.9

Now, save and close the XML file.

Next, right-click on the already created connection in Excel, and click on the Refresh button.

Web Url.10

As soon as we press Refresh, the data will be updated automatically.

Web Url.11

Likewise, we can use power query and make it easier to work with XML data.

How to Add XML Map to Excel?

Once the source XML file is connected to Excel, we can map the required columns in Excel. For instance, we have loaded the following XML file already to Excel.

Add Map

There are 4 columns in the XML data file. However, we do not necessarily need to load all 4 columns to Excel.

Out of these 4, we can choose the columns we want to see in the Excel worksheet.

Now, right-click on the column we want to map and click on Map Element.

Add Map.1

Now, we should choose the targeted cell in the worksheet.

Add Map.2

Next, click OK to create a mapping column in the selected cell.

Add Map.3

Similarly, we can choose only the necessary columns and create a mapping in Excel.

For instance, we have mapped the following 3 columns, which are bold in the XML source window.

Add Map.4

We can also map columns. Select the column and double-click on the column to map it.

Important things to note

  • XML stands for Extensible Markup Language.
  • We should add the code, <data-set xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”> at the top of every XML file.
  • Also, XML data should always contain the word </data-set>
  • XML format data should not contain any space in its headers, and each header should be included within the tag (<>).
  • Each header should contain the backward slash (/) to close the particular entry.
  • XML data can be stored in a notepad, but while saving the file, we need to give the file extension as .xml.

Frequently Asked Questions

How do I use XML in Excel?


If we have the XML file in our system, we can import it with a few steps.

For instance, we have the following sample data in the XML file.

FAQ 1

Now, let us learn how to use XML in excel.

The steps required to use XML in excel are as follows:

Step 1: First, go to the Developer tab and click on Source.

Import.5

Step 2: Next, click on XML Maps.

XML in Excel Import.6

Step 3: Then, click on the Add… button.

XML in Excel Import.7

Step 4: Choose the file from the saved folder.

Step 5: Next, click on OK. We can see that the XML file is mapped to the Excel file.

FAQ 1.1

Step 6: Then, select all the columns and right-click on the same.
Now, click on Map element… option from the available options.

FAQ 1.2

Step 7: Next, excel will create a table format, as shown in the following image.

FAQ 1.3

Step 8: Then, under the Developer tab, click on Import.

Developer tab (Import)

Step 9: Next, import the same file that we imported earlier.

Step 10: Once the import is done, we will see the data in table format.

FAQ 1.4
Likewise, we can use XML in excel.

How to open XML in Excel by Default?


To open XML in excel by default, we need to make some changes.

Step 1: First, type Windows + R and run regedit.exe.

Step 2: Next, navigate to the folders in the following path.

hkey_current_user/Software/Microsoft/Windows/CurrentVersion/Explorer/FileExts/.xml/OpenWithList/

Step 3: Then, choose EXCEL.EXE

Step 4: Next, restart the computer.

Likewise, we can open XML in Excel.

Why is the XML file not opening in Excel?

XML file opens with notepad, and we can import the data into Excel. However, if there is a problem opening the XML file with Excel, then we need to change the default settings in our system.

Download Template

This article must be helpful to understanding XML in Excel, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to XML in Excel. Here we discuss how to import and convert XML in excel (top 3 methods) along with examples and downloadable template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published.