How to Save Excel File As XML Data Mapping (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for how to save Excel as XML data mapping, then you are in the right place. XML is a markup language that works as same as HTML. XML is designed to store and transport data. Using Excel to store data in XML format allows us to store our data on the web and make it accessible to other users. In this article, we’ll try to discuss how to save Excel as XML data mapping.


Save Excel File As XML Data Mapping: 5 Steps

We need to prepare an Excel dataset and convert it to XML while saving it as XML data mapping in excel. This is done by following some simple steps.
For creating this article, we have used Microsoft Excel 365 version, but you can use any other version according to your preference.


1. Making a Dataset in Spreadsheet to Save Excel File As XML Data Mapping

Firstly, we have made a dataset named List of Employees. The dataset has column headers like Name of Employee, Working Hours, and Hourly Pay Rate. The dataset is like this.

excel save as xml data mapping

Now, we’ll convert this dataset to an XML file.


2. Saving a Code in Notepad As XML Schema

In this step, firstly, we’ll write our XML schema in a text editor. Eventually, here, we have written the schema in Notepad. The schema is like this.

Saving a Code in Notepad As XML Schema for XML data mapping

Additionally, we haven’t used any space between the words of column headers. We have added headers like NameofEmployee, WorkingHours, etc., but not like Name of Employee, Working Hours. Because if we use space, then this schema will not work to add in the XML Maps.
Secondly, we need to save this in an XML format.
For this, go to File > choose Save As.

Saving a Code in Notepad As XML Schema for XML data mapping

Thirdly, give the name of the file. We have given the name Excel-to-XML.xml.
Fourthly, click Save.


3. Opening XML Source Panel and Adding XML Map

In this step, firstly, we need to open the XML Source panel.
To do this, initially, go to the Developer tab > select Source.

Opening XML Source Panel and Adding XML Map

Eventually, an XML Source panel will appear like this.
Secondly, click on XML Maps located at the right-bottom of the XML Source panel.

Opening XML Source Panel

Consequently, an XML Maps window will appear.
Thirdly, click Add.

Opening XML Source Panel

Fourthly, select the Excel-to-XML.xml file from the specific location of the PC and click Open.

Adding xml map for Opening XML Source Panel in XML data mapping

Eventually, the XML file will be added to the XML Maps window.
Fifthly, select the file > click OK.

Adding xml map

Consequently, we’ll see that the elements of our dataset will appear in the XML Source panel.

Adding xml map


4. Integrating XML Elements into Existing Dataset to Save Excel File As XML Data Mapping

In this step, we’ll integrate the XML elements into the dataset.
To do this, first, select the first-row header of the dataset. Here, we have selected the Name of the Employee.
Secondly, double-click the element having the same name from the XML Source panel.

Integrating XML Elements into Existing Dataset

Eventually, we will see that the header and the dataset under it have turned into a table. We have shown the dropdown symbol under the converted column by the red mark in the picture below.

Additionally, repeat the same procedure for column headers of Working Hours and Hourly Pay Rate.
Consequently, we’ll find the output like this.

Read More: How to Create Mind Map from Data in Excel


5. Exporting Excel File As XML File to Save Excel File As XML Data Mapping

Finally, in this step, we’ll export the Excel dataset as an XML file.
To do this, firstly, go to the Developer tab > click Export.

excel save as xml data mapping

Secondly, we’ll notice that the file already has a .xml extension.
Thirdly, select the file > click Export.

Finally, the existing dataset is turned into XML.


Things to Remember

  • We should not add any space between the words of column headers in the code of Notepad.
  • We should give the same name in the code as the name given to Notepad while saving.
  • We need to select All Files when we save Notepad.

Download Practice Workbook


Conclusion

We can save Excel files as XML data mapping efficiently if we study this article properly.


Related Articles

<< Go Back To Data Mapping in Excel | Importing Data in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shajratul Alam Towhid
Shajratul Alam Towhid

Md Shajratul Alam Towhid, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, holds a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep passion for research and innovation, he actively engages with Excel. In his capacity, Towhid not only adeptly tackles complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his unwavering commitment to consistently delivering exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo