How to Export Excel to XML? (An Ultimate Guide)

Exporting data from Excel to XML (Extensible Markup Language) is a valuable process that enables the interchange of structured information between different platforms and applications. By converting Excel data to XML format, users can enhance data portability, facilitate data sharing, and support interoperability. In this guide, we’ll explore step-by-step instructions on how to seamlessly export your Excel data to XML, unlocking the potential for improved data integration and collaboration. Let’s dive into the intricacies of this versatile process.

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 work fine even if you add or remove data.
XML makes things like 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 v/s 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. This 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 effortlessly export the updated data back to the XML Data File.

Writing an XML element is easy. Follow the below rules:

Each element should have an opening tag (<entry>) and a corresponding closing tag (</enrty>).
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

In this section, we will convert and export Excel data to XML data.

To export Excel data to XML we need to convert Excel data first. Select the range B4:G17 >> tap File.

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

As you can see, Excel does not allow to save XML data if data do not have any XML mappings.

We have to create an XML Map to fix this. Now, in a preferred text editor, let’s 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.

Now, open the Excel file >> go to Developer tab >> click Source >> XML Maps.

Tap on Add in XML Maps dialog box.

Locate and select the XML file >> Open.

Subsequently, the map will appear in XML Source pane >> drag each value to their respective headers as shown below.

Finally, click Export in Developer tab.

Thus, the Excel file is converted and exported to XML file.

How to Connect XML Data to Excel

In this section, we will discuss how to connect any XML file with Excel file. Here, we will extract any data from the XML file, we will just connect the XML file to Excel.

1. Create XML Table to Import XML Data

To connect XML data, you can initially create an XML table by clicking Developer tab >> Import.

Select desired XML file >> Import.

Now, in Import Data dialog box, select XML table in existing worksheet box >> choose cell B6 >> OK.

Thus, the link to the XML file appears in the worksheet.

2. Import XML Data Files as External Data

Another method of connecting XML data is importing them as external data.

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

After selecting the desired XLM file, click on Load in the dialog box.

Hence, we connect XML file with Excel.

Things to Remember

Data must be enclosed between matching tags, such as <Entry> data </Entry> (start and end with identical tags).
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. Is XML the same as PDF?
No, PDF is not same as XML.

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

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

Conclusion

In summary, we have explored a guide to Excel XML. We have learned how to convert, import, and export XML files to Excel and vice versa. Hopefully, you can implement this and use it in your project. If you have any suggestions, let us know in the comment box. Thank you for reading.

Export Excel to XML: Knowledge Hub

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

Get FREE Advanced Excel Exercises with Solutions!