XML in Excel

XML in Excel – Extensible Markup Language

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.

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

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 a 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 PayrollPayrollPayroll refers to the overall compensation payable by any organization to its employees on a certain date for a specific period of services they have provided in the entity. This total net pay comprises salary, wages, bonus, commission, deduction, perquisites, and other benefits.read more, POS (Point of Sale), Inventory Software’s, SQL Servers, etc.…

XML in Excel

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: XML in Excel (wallstreetmojo.com)

Like every other database software, XML has its own rule and regulations. Below are some of the common rules 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? (with Examples)

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

Example #1 – Website (XML data) Data Download

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 webserver to a worksheet of excel.

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

  1. Open the XE.com website.

  2. Search for Historical Currency Rates.

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

  4. Go to the Data tab in excel and under getting External Data, select From Web option.

    xml excel example 1.4

  5. Once you click on this, you will see this window.

    xml excel example 1.5

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

    example 1.6

  7. After clicking on Go, you will see the webpage.

    xml excel example 1.7

  8. Once you see the result click on Import.

    xml excel example 1.8

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

    xml excel example 1.9

  10. After selecting the cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more, excel will store the XML data as it is on the website and will take a few seconds to complete it.

    example 1.10

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

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 the below code and paste.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Name>Amar Jyothi</Name>
<Salary>17061</Salary> <Department>Marketing</Department>
  • Step 2: Save this as data-set.xml
example 2.2

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.

xml.excel example 2.3
  • Step 4: Under XML Source right-click on the XML Maps.
xml.excel example 2.4
  • Step 5: Select the file you want to add.
example 2.5
  • Step 6: Select the desired file and click on the OK button.
xml.excel example 2.6

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

xml.excel example 2.7

Things to Remember

  • 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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion