WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » XML in Excel

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 Payroll, POS (Point of Sale), Inventory Software’s, SQL Servers, etc.…

xml.excel

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

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course
  • 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.

  • Step 1: Open the 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 the Data tab in excel and under getting External Data, select From Web option.

xml excel example 1.4

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

xml excel example 1.5

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

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

xml excel example 1.7

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

xml excel example 1.8

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

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

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">
<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

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.

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

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 –

  • Open XML File in Excel
  • VBA LEN
  • File Formats in Excel
  • How to Import Data into Excel?
  • Share an Excel Workbook
11 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More