In order export an Excel 2010 worksheet to XML you have to add XML Mappings to the file. This process is a bit different from previous Excel versions so here is a step by step on how to achieve this.
For this blog I have an Excel 2010 file containing some news articles that I would like in XML format.
Step 1
Create an .xsd file for the XML Mappings
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="NewsItemsTable">
<xs:complexType>
<xs:sequence>
<xs:element ref="NewsItemRow" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="NewsItemRow">
<xs:complexType>
<xs:sequence>
<xs:element name="newstitle" type="xs:string"/>
<xs:element name="newsdate" type="xs:dateTime"/>
<xs:element name="newssummary" type="xs:string"/>
<xs:element name="newstext" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
NOTE:
The .xsd file contains the mappings for our XML. For this tutorial I have added fields for news articles, which match the Excel file I will be using. Note the different types of elements we will be using.
Step 2
Add the developer toolbar to the ribbon
Select File menu, then Options.
On the “Customize Ribbon” tab, check “Developer” tab in the right column.
Step 3
Add the .xsd file as an XML Source
Click the Developer toolbar and select “Source” to display the “XML Source” pane.
Click “XML Maps” to add the .xsd file.
Select the root for the mappings as “NewsItemsTable”
Confirm the XML Map was imported correctly
NOTE:
Be sure your XML Mappings Options are marked to “Automatically Merge Elements When Mapping” and “My Data Has Headings”
Step 4
Map the columns to elements
Select the columns you would like to map in the Excel file
In the “XML Source” pane, right click the “NewsItemsTable” node and select “Map element”
Confirm the columns were mapped correctly
Step 5
Export to XML
On the “Developer toolbar”, click “Export”
You will now have an XML file containing the Excel data with the mappings you defined.
