A Complete Guide to Map an XML File in Excel
To export your Excel file as an XML document, you must map your XML document with your Excel worksheet. Without mapping, Excel can’t figure out how data from your Excel worksheet will be stored in the XML file. Thus, mapping is a mandatory step to export an Excel file as an XML file. Thus, I’ve come up with this article to guide you to map and XML documents in Excel. I hope, you will find this article beneficial to you.
What is XML Mapping in Excel?
XML mapping in Excel is the process of creating a mapping between an XML document and an Excel workbook. This mapping allows data from the XML document to be imported into the Excel workbook, and data from the Excel workbook to be exported to the XML document.
XML mapping in Excel can be used to import and export data between an XML document and an Excel workbook. To map an XML document to an Excel workbook, you need to add an XML schema to the workbook.
About the Setup
I’ve created the following dataset in Microsoft Excel. Now I want to export the dataset as an XML file.
To export an Excel file as an XML document, I need to create an XML schema first.
I’ve created the following XML schema to export my Excel file.
If you don’t know how to create an XML schema, please check out this link to learn to create an XML schema for Excel.
<?xml version='1.0' encoding='UTF-8'?>
<dataset>
<record>
<FirstName>Ulrick</FirstName>
<LastName>Copner</LastName>
<Email>ucopner0@naver.com</Email>
<Gender>Male</Gender>
</record>
<record>
<FirstName>Tabbie</FirstName>
<LastName>Aysik</LastName>
<Email>taysik1@topsy.com</Email>
<Gender>Female</Gender>
</record>
</dataset>Guide to Map XML in Excel
To work with XML files, you have to select the Developer tab. Under the Developer tab, you will get all the necessary commands in the XML group.
By default, the Developer tab is not activated in the main ribbon. However, if you don’t see the Developer tab in the main ribbon, read 2 ways to add the Developer tab in Excel.
Add an XML Schema Source
Before mapping an XML schema, you need to add an XML schema source first.
To do that,
Step_1: Select the Developer tab first.
Step_2: Now click on the Source command in the XML group.
The XML Source task pane will appear.
Step_3: Click on the XML Maps command at the bottom of the XML Source task pane.
XML Maps dialog box will pop up.
Step_4: Click on the Add button to add an XML schema source.
After selecting an XML schema, the following dialog box will show up.
Step_5: Just hit the OK button to proceed.
Now you will see that your selected XML schema has been added to the XML Maps dialog box.
Step_6: Just hit the OK button to proceed.
Now the column names in the XML schema will show up in the XML Source task pane.
Map XML Document to Excel File
Once you have all the column names visible in the XML Source task pane, you can start mapping them.
To map,
Step_1: Select the first column name in the XML Source task pane.
Step_2: Then drag it all the way and drop it on the corresponding column header in the Excel worksheet.
For example, Drag FirstName from the XML Source task pane and drop it on the FirstName column header in the Excel sheet.
Step_3: Repeat Step_1&2 for the rest of the column names.
This is how you can do that mapping with your XML schema to your Excel worksheet.
To check whether the mapping has been successful or not,
- Click on any of the column names in the XML Source task pane.
- The corresponding column will be automatically selected in the Excel worksheet.
For example, if I click on the LastName column header in the XML Source task pane, the LastName column in the Excel worksheet is automatically highlighted.
When the mapping is done successfully, the column names in the XML Source task pane will look bold also. This is another way to check that the mapping has been successfully completed.
Conclusion
I tried to demonstrate how to map an XML document with an Excel worksheet step by step. I hope this article evaporates all of your confusion. If you still have any issues regarding mapping an XML schema to your Excel worksheet, feel free the describe them in the comment section. Thanks!
Related Articles
![How to Remove Duplicates in Excel [14+ Different Methods]](https://excelgraduate.com/wp-content/uploads/2022/06/Remove-Duplicates-in-Excel-3.png)
