How to Map an XML File in Excel [Step-by-Step Guide]
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.
Now let’s talk about the arrangement. 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 read how 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>Steps to Map an XML File 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.
Step 1: Add an XML Schema Source
Before mapping an XML schema, you need to add an XML schema source first.
To do that:
- Select the Developer tab first.
- Now click on the Source command in the XML group.
The XML Source task pane will appear. - Click on the XML Maps command at the bottom of the XML Source task pane. The XML Maps dialog box will pop up.
- Click on the Add button to add an XML schema source.
After selecting an XML schema, the following dialog box will show up. - 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. - Just hit the OK button to proceed.
Now the column names in the XML schema will show up in the XML Source task pane.
Step 2: 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 an XML document to an Excel file:
- Select the first column name in the XML Source task pane.
- 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. - Repeat Steps 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!
Frequently Asked Questions
How do I share an Excel workbook with XML maps?
To share an Excel workbook with XML maps, you can do the following:
- Map XML elements to cells.
- Import XML data into your Excel sheet.
- Remove all XML maps from your Excel workbook.
- Share the workbook now.
How do I find XML maps in Excel?
To find XML maps in Excel, you can use the XML Source task pane:
- Select the Developer tab.
- In the XML group, click Source.
- Click XML Maps in the XML Source task pane.
Related Articles
When i map XML then desine tab not it map only heading
If the “Developer” tab is not visible in Excel when you are working with XML mapping, it may be because the “Developer” tab is not enabled in your Excel application.