Excel 2019

How to Export XML Data

In this tutorial we’ll discuss how to export your worksheet data as an XML file. The first method is, click File > Save As > Browse and from Save As dialog box select the XML Data (*.xml) in the Save as type. The second method is, click Developer > Export. To import and export XML data, Excel required at least one XML map (XML schema .xsd file) attached to the workbook. Click Developer > Source and then use the XML Source task pane to attach schema files and map XML elements of the schema to individual cells or tables.

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.)
Source button to open XML Source task pane

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:
Blank XML Source task pane

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:
XML Maps Dialog Box

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:
Select XML Source Dialog Box

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:
XML Maps dialog box

The XML Source task pane now displays the structure of the XML map:
XML Source task pane showing map structure

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:
Drag and drop xml elements

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:
Expand xml table

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:
Save As dialog box

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:
Developer Export button

Navigate to the folder in which you want to export your data, enter a name for the file and click Export:
Exprt XML dialog box

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
    Export button greyed out

    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:
    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:
    Excel creates schema based on 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.

Advertisement:
Advertisement: