Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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


Download Practice Workbook


5 Steps to Save Excel File As XML Data Mapping

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

Read More: How to Create Mind Map from Data in Excel (2 Common Ways)


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 words of column headers. We have added the 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 add 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.
For doing this, firstly, select the first-row header of the dataset. Here, we have selected the Name of Employee.
Secondly, double click 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.


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

Conclusion

We can save Excel files as XML data mapping efficiently if we study this article properly. Please feel free to visit our official Excel learning platform ExcelDemy for further queries.


Related Articles

Towhid

Towhid

Hello, myself Shajratul Alam Towhid. Basically, I am a Naval Architect who wants to expand knowledge in the field of Microsoft Excel. I wish all of my articles will be beneficial for the readers.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo