How to Export XML Data

• 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
  • Export button is disabled
  • The XML maps in this workbook are not exportable
  • Workbook does not contain any XML mappings
  • I don’t have XML schema (.xsd extension) 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. The map.xsd file has a single root node so the Multiple 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

1. 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 the Developer > Source and click XML maps button from the XML 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.

2. 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 the Developer > Source and select the elements or entire nodes in the XML map and drag them onto the worksheet.

3. 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 the Developer > Source and select the elements or entire nodes in the XML map and drag them onto the worksheet. If the XML Source task pane is blank, click XML maps button from the XML Source task pane to add XML maps.

4. 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 use pets.xml file, created in Introducing XML tutorial:

   Open XML Source task pane by clicking the Developer > Source and click XML maps button from the XML Source task pane to add XML maps. The XML Maps dialog appears, click Add, select pets.xml file and click Open. 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 write Workbook_AfterXMLExport and Workbook_BeforeXMLExport event listener procedures for these events to control what happens before and after import.