XML in Excel

XML are the files used in the databases which are shared over the web, excel has made it very easier for us to import the data in XML to excel in the form of tables or database, XML is basically an external data and it can be imported to excel from the data tab under the get external data tab from data from other sources section.

XML in Excel (Table of Contents)

• Extensible Markup Language
• Download XML Data to Excel
• Add XML Schema to Excel

XML in Excel – Extensible Markup Language

Data is the platform where almost all the business entities revolve around. In the modern world, all the businesses use different platform and programs to store the data for analysis and decision making purpose.

XML – Extensible Markup Language is the most common format to work and analyses the data in this modern world. Excel allows us to convert the spreadsheet data into XML files and getting started is the strange thing for common excel users.

XML is a structured data and one of the external data source. We create a link or import data from outside where data resides outside the spreadsheet. We have many external databases like Payroll, POS (Point of Sale), Inventory Software’s, SQL Servers, etc.…

Like every other database software’s XML too has its own rule and regulations. Below are some of the common rule for XML.

• While writing the code data both the head (start) and tail (end) should have similar tag names. For Example

<TableName> Data </TableName>

• Both head and tail are case sensitive.

How to Import XML Data into Excel?

Let’s see how to import XML data into excel.

XML in Excel Example #1

Website (XML data) Data Download Example 

When we are searching on the internet we may find some of the useful information on the web and we need that data dump to the worksheet of excel. In this article, I will show you how to download the data from the web server to a worksheet of excel.

Assume you are working with foreign markets regularly and you need the data of all the currency exchange rate for INR. Usually, I rely on XE.com for all the historical currency rates. To download the currency rates for 17^th Jan 2019 follow the below steps.

Step 1: Open XE.com website.

Step 2: Search for Historical Currency Rates.

Step 3: Select the currency & date, which you want the data for. I have selected INR and 17-04-2019.

Step 4: Go to Data tab in excel and under Get External Data select From Web option.

Step 5: Once you click on this you will see this window.

Step 6: Now copy the URL you have created in the XE.com and paste in the Address section and click on Go.

Step 7: After clicking on Go you will see the webpage.

Step 8: Once you see the result click on Import.

Step 9: After the button IMPORT is clicked excel will ask you where to store the data. Select A1 as the cell reference.

Step 10: After selecting the cell reference, excel will store the XML data as it is on the website and will take a few seconds to complete it.

In this way, we can download the data from web servers and have our data in the spreadsheet itself.

XML in Excel Example #2

Add XML (Markup Schema) to Excel

In order to add XML Schema, we need perfect column headings. XML Schema can be created by using column headings.

Step 1: Open Notepad and copy below code and paste.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<record>
<Name>Amar Jyothi</Name>
<Salary>14051</Salary>
<Department>Sales</Department>
</record>
<record>
<Name>Radha</Name>
<Salary>17061</Salary> <Department>Marketing</Department>
</record>
</data-set>

Step 2: Save this as data-set.xml

It is important to as two pieces of information from the data. I have added two name’s data to the list. It is an indication of the excel to repeat throughout the worksheet.

Step 3: Now make sure the Developer tab is enabled in your Excel ribbon and select Source from the Developer tab.

Step 4: Under XML Source right click on the XML Maps.

Step 5: Select the file you want to add.

Step 6: Select the desired file and click on OK button.

Once you click on the OK button you will see the following output.

Things to Remember about XML in Excel

• XML is one of the structured languages for a database.
• XML is Extensible Markup Language.
• We can import and export under XML to the spreadsheet and spreadsheet to the XML.
• Head and tails should be the same under notepad coding.
• You need to save the file as.XML
• XML is available as add-ins too. Search on the internet for more information regarding this.

Recommended Articles

This has been a guide to what is XML in Excel. Here we discuss how to import and add XML Markup Schema to excel along with practical examples. You can learn more about Excel from the following articles –

• VBA LEN as Support Function
• File Formats in Excel
• DIR Function in VBA
• How to Import Data into Excel?
• Excel for Finance Course