How to Export Excel to XML – A Guide

Introduction to Extensible Markup Language (XML)

XML, a markup language similar to HTML, is specifically designed to store and transport data. Here are some of its properties:

  • XML is like data with tags around it.
  • Most XML programs allow you to add or remove data.
  • XML makes moving data, sharing, and adapting to different devices easier.
  • The XML standard is flexible and simple for creating information formats.
  • With XML, we can share organized data online or in companies.
  • XML organizes data in a hierarchical or tree-like structure.

XML Data and Schema Files

Excel generally works with 2 types of XML data:

  • XML data files (.xml): stores information with custom tags and structured data.
  • Schema files (.xsd): contains schema tags that define rules, like data type and validation, to enforce a specific structure for the XML

XML vs XSD

  • XML schema language is commonly known as XML Schema Definition (XSD).
  • XSD is used to define the structure of an XML file that needs to be created and specifies where the columns should be mapped in Excel’s tabular data.
  • XSD can also be referred to as Extensible Stylesheet Language Transformation (XSLT) or as a .xslt file.
  • XSD is responsible for implementing data types and validation rules in the file.
  • With XSD, you can create elements, define data relationships, and specify data validation within the XML file.
  • XML data files (.xml) store information with custom tags and structured data.

How to Use XML Data in Excel

In Excel, you can add an XML Schema File (.xsd) to your workbook. It allows you to map specific XML Schema elements to individual cells or XML tables within the Excel sheet. By importing an XML Data File (.xml) and binding XML elements to the previously mapped cells, you can preserve the XML structure and its corresponding definitions. After making revisions to the data within the mapped cells, you can export the updated data back to the XML Data File.

To write an XML element:

  • Each element should have an opening tag (<entry>) and a corresponding closing tag (</entry>).
  • All tags in an XML file must be enclosed within angle brackets ("<" and ">"), with the tag name placed between these characters. To create a closing tag, simply add a slash ("/") after the opening angle bracket.

How to Export Excel Data to XML Data

  • Convert Excel data first. Select  B4:G17 >> Go to File.

Selecting range click File tab

  • Click Save As >> select XML Data (*.xml) >> Save.

Save As in file in XML format

  • Excel does not allow saving XML data if data has no XML mappings.

Excel sends an alert due to missing XML mappings

  • Create an XML Map. Create a blank file and add XML header information as mentioned above. Here is the full code:
<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<feed>
<title type="text">DailyTreasuryRealYieldCurveRateData</title>
<entry>
<title type="text"></title>
<updated>2023-08-02T15:54:04Z</updated>
<author><name /></author>
<m:properties>
<d:NEW_DATE m:type="Edm.DateTime">2023-08-01T00:00:00</d:NEW_DATE>
<d:TC_5YEAR m:type="Edm.Double">1.98</d:TC_5YEAR>
<d:TC_7YEAR m:type="Edm.Double">1.80</d:TC_7YEAR>
<d:TC_10YEAR m:type="Edm.Double">1.68</d:TC_10YEAR>
<d:TC_20YEAR m:type="Edm.Double">1.72</d:TC_20YEAR>
<d:TC_30YEAR m:type="Edm.Double">1.81</d:TC_30YEAR>
</m:properties>
</content>
</entry>
</feed>
  • Save and close the file.

14-XML format and structure in note pad

  • Open the Excel file >> go to Developer tab >> click Source >> XML Maps.

XML Maps option in Excel

  • Click Add in the XML Maps dialog box.

Add option in XML Maps dialog in Excel

  • Locate and select the XML file >> Open.

XML Export file selected

  • The map will appear in the XML Source pane >> drag each value to the respective header as shown below.

Dragging data to XML Source to worksheet

  • Click Export in Developer.

Export option in Developer tab

  • The Excel file is converted and exported to XML file.

XML data obtained from Excel data


How to Connect XML Data to Excel

1. Create an XML Table to Import XML Data

  • Go to the Developer tab >> Import.

Import feature in Developer tab

  • Select the XML file >> Import.

XML files imported to Excel

  • In the Import Data dialog box, select XML table in existing worksheet >> choose B6 >> OK.

Selecting range in Import Data dialog

The link to the XML file is displayed in the worksheet.

XML file link appears in worksheet


2. Import XML Data Files as External Data

  • Go to Data >> Get Data >> From File >> From XML.

From XML option in Get Data

  • Select the XLM file, click Load in the dialog box.

Load to import XML data

The XML file is connected to Excel.

XML data appears as external source


Things to Remember

  • Pay attention to the case sensitivity of tag names, ensuring that the capitalization in the end tag matches the beginning tag.
  • An XML file should always begin and end with a root tag, and there can only be one root tag in the file.
  • Empty tags are allowed, denoted by placing a slash at the end of the opening tag, like <Entry> </Entry>.

Frequently Asked Questions

1. Can I edit XLM in Excel?
As you select cells in the worksheet, the different nodes will be highlighted in the XML Source pane, so you can see what is being referred to. You can alter the different values and save the document as an XML document.

2. What are the benefits of using XML in Excel for data exchange?
Transferring XML data in Excel allows combining data from various sources and systems into a single spreadsheet. Also, it provides a structured format for data, making it easier to organize and manage information within Excel.


Export Excel to XML: Knowledge Hub

<< Go Back to Export Data from Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Yousuf Khan
Yousuf Khan

Yousuf Khan has been working with the ExcelDemy project for over one year. He has written 47+ articles for the ExcelDemy site. He also solved a lot of user problems with ExcelDemy Forum. He has interests in Excel & VBA, Desktop and mobile applications, and projects & templates. He completed his graduation and post-graduation in Information Technology from Jahangirnagar University. Currently, he works as a VBA & Excel Content Developer in ExcelDemy projects, writing unique and informative content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo