- This tutorial covers the following topics:
- Creating XML Maps
- Save workbook as XML Data to export its data
- Export XML data using
Developer > Export
- Creating the XML Schema File
- Errors while exporting data to XML file
XML Maps
XML schemas in Excel are called XML maps. You can associate single or multiple schemas with a workbook and then map the schema elements to cells on the worksheet. To create XML map, open the XML Source
task pane by clicking the Developer > Source
. (If you don’t see the Developer tab, see Show the Developer tab.)
Excel displays the XML Source
task pane. The XML Source
task pane is blank because the current worksheet doesn’t have any XML maps associated with it:
The XML Source
task pane is used for displaying XML maps found in the XML data or schema documents, and mapping XML elements to cells or ranges on a worksheet. In the XML Source
task pane, click the XML Maps
button to open the XML Maps
dialog box as shown in following figure:
The XML Maps dialog box is used to add, delete, or rename an XML map associated with the workbook. Click the Add button and select an XML schema (.xsd extension) file and click Open:
If the XML schema file have more than one root node, the
Multiple Roots
dialog box appears and you must select a root node which you wan to use. Themap.xsd
file has a single root node so theMultiple Roots
dialog box will not appear.
Excel displays the XML map name (root element name is small letters) in the XML Maps dialog box shown in following figure, click OK to return to Excel window:
The XML Source
task pane now displays the structure of the XML map:
To create the XML table, place all of the XML elements on the worksheet by dragging the root element pets from the XML Source
pane and dropping it on a cell on the sheet:
To add rows in XML table, move mouse pointer to bottom-right corner of the table, click and hold the mouse button when double headed arrows appears, drag the pointer to downside and drop pointer at desired cell:
The XML table is mapped to the Excel cells. Fill all rows and try to export the table or workbook as an XML document using one of the following methods.
Save workbook as XML Data to export its data
Click File > Save As > Browse
and select the location where you want to save the XML file. Write a file name for you XML file in File name
field and select XML Data (*.xml)
from the Save as type
list, as shown in following figure:
Export XML from the Developer tab
To export XML data, click any cell in the XML table and then, on the Developer
tab, click the Export
button to display the Export XML
dialog box:
Navigate to the folder in which you want to export your data, enter a name for the file and click Export:
Creating an XML Map (Schema)
An XML map is actually an XML schema (plain text document) which helps the other application to understand the elements in the XML document. Excel allows you to add multiple XML maps to a workbook. If you want to create XML schema used in this tutorials, open a text editor, copy following code, and paste into the text editor and save it as map.xsd
:
<?xml version="1.0" encoding="utf-8"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="pets"> <xs:complexType> <xs:sequence> <xs:element maxOccurs="unbounded" name="Pet"> <xs:complexType> <xs:simpleContent> <xs:extension base="xs:string"> <xs:attribute name="Type" type="xs:string" /> <xs:attribute name="Age" type="xs:integer" /> </xs:extension> </xs:simpleContent> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>
The map.xsd
XML scheme (map) defines the elements of the pets
XML. Read the full tutorial on XML schema: Creating XML Schema.
Errors when export data as XML
Developer Tab > Export button is disabled
Solutions: This error occurs because an XML map is not yet added. Open
XML Source
task pane by clicking theDeveloper > Source
and click XML maps button from theXML Source
task pane to add XML maps. After adding the XML map, select the elements or entire nodes in the XML map and drag them onto the worksheet.Cannot save or export XML data. The XML maps in this workbook are not exportable:
Solution: This error occurs because an XML map is not yet applied. Open
XML Source
task pane by clicking theDeveloper > Source
and select the elements or entire nodes in the XML map and drag them onto the worksheet.Can not save XML data because the workbook does not contain any XML mappings:
This problem occurs when you try to specify “XML Data (.xml)” in the “Save as type” list.
Solutions: This error occurs because an XML map is not yet applied. Open
XML Source
task pane by clicking theDeveloper > Source
and select the elements or entire nodes in the XML map and drag them onto the worksheet. If theXML Source
task pane is blank, click XML maps button from the XML Source task pane to add XML maps.I don’t have XML schema (.xsd extension) file to create an XML map
Solution: If you don’t have an XML schema (
.xsd
extension) file but you’ve a sample XML data file then you can use this sample XML file to create an XML map. For example, you can usepets.xml
file, created in Introducing XML tutorial:Open
XML Source
task pane by clicking theDeveloper > Source
and clickXML maps
button from the XML Source task pane to add XML maps. The XML Maps dialog appears, clickAdd
, selectpets.xml
file and clickOpen
. Excel will automatically creates a schema based on the source data:
Click Ok to create the map.
When you export data to an XML file, the two XML events
AfterXMLExport
and BeforeXMLExport triggered. You can writeWorkbook_AfterXMLExport
and Workbook_BeforeXMLExport event listener procedures for these events to control what happens before and after import.