Export XML data

After you have imported XML data, mapped the data to cells in your worksheet, and made changes to the data, you often want to export or save the data to an XML file. There are two ways to export XML data from a Microsoft Office Excel 2007 worksheet:

  • We recommend using the Export command in the XML group on the Developer tab to get XML data out of your worksheet.
  • For backward compatibility with earlier XML functionality, you can still use the Other Formats command on the Save As submenu of the Microsoft Office Button Button image.

 Important    When creating XML maps and exporting data in Excel to XML files, there is a limit on the number of rows that can be exported. Export to XML from Excel will save a maximum of 65536 rows. If your file contains more than 65536 rows, Excel will export the first (#ROWS mod 65537) rows. For example, if your worksheet has 70000 rows, Excel will export 70000 mod 65537 = 4464 rows. Our recommendation is to 1) use xlsx instead or 2) save the file as XML Spreadsheet 2003 (.xml), which will lose the mappings, or 3) delete all rows after 65536 and then export again which will keep the mappings but lose the data at the end of the file.

In this article


The basic process of using XML data in Excel

The following diagram shows how the different files and operations work together when you use XML with Excel. Essentially, there are five phases to the process:


Overview of how Excel works with XML data

Callout 1 Adding an XML schema file (.xsd) to a workbook
Callout 2 Mapping XML schema elements to individual cells or XML tables
Callout 3 Importing an XML data file (.xml) and binding the XML elements to mapped cells
Callout 4 Entering data, moving mapped cells, and leveraging Excel functionality, while preserving XML structure and definitions
Callout 5 Exporting revised data from mapped cells to an XML data file

Top of Page Top of Page

Export XML data in mapped cells to an XML data file

  1. If the Developer tab is not available, do the following to display it:
  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

     Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. On the Developer tab, in the XML group, click Export.
    XML group in Ribbon

     Note   If a small Export XML dialog box appears, click the XML map that you want to use, and then click OK.


    The Export XML dialog box will only appear if an XML table is not selected and if the workbook contains more than one XML map.
  2. In the large Export XML dialog box, in the File name box, type a name for the XML data file.
  3. Click Export.

 Note   There may be headings or labels in your worksheet that are different from the XML element names in the XML map. However, the XML element names are always used when you export or save XML data from the worksheet.

Top of Page Top of Page

Save XML data in mapped cells to an XML data file

  1. Click the Microsoft Office Button Button image, and then point to the arrow next to Save.

This step ensures that any changes that are made to your workbook won't be lost when the workbook is saved as an XML data file.

  1. On the Microsoft Office Button Button image, point to the arrow next to Save As, and then click Other Formats.
  2. In the File name box, type a name for the XML data file.
  3. In the Save as type list, click XML Data, and then click Save.
  4. If you receive an alert stating that saving the file as XML data may result in the loss of features, click Continue.

 Note   If a small Export XML dialog box appears, click the XML map that you want to use, and then click OK.

The Export XML dialog box will only appear if an XML table is not selected and the workbook contains more than one XML map.

  1. In the large Export XML dialog box, in the File name box, type a name for the XML data file.
  2. Click Export.

 Note   There may be headings or labels in your worksheet that are different from the XML element names in the XML map. However, the XML element names are always used when you export or save XML data from the worksheet.

Top of Page Top of Page

Issues with exporting XML data

The following sections explain issues that you can encounter when you export XML data.

I get a message saying that the map can be exported but some required elements are not mapped.

There may be several reasons for this message:

  • The XML map that is associated with this XML table contains one or more required elements that are not mapped to the XML table.

The hierarchical list of elements in the XML source task pane indicates required elements by the red asterisk on the top-right corner of the icon to the left of each element. To map a required element, drag it to the worksheet location where you want it to appear.

  • The element is a recursive structure.

A common example of a recursive structure is a hierarchy of employees and managers in which the same XML elements are nested several levels. Although you may have mapped all of the elements in the XML Source task pane, Excel does not support recursive structures more than one level deep and therefore cannot map all of the elements.

  • The XML table contains mixed content.

Mixed content occurs when an element contains a child element and simple text outside of a child element. One common case is where formatting tags (such as the bold tags) are being used to mark up data within an element. Although the child element (if Excel supports it) can be displayed, the text content will be lost when the data is imported, is not available when the data is exported, and therefore cannot be round-tripped.

When I export XML data, a message tells me that the XML maps in my workbook are not exportable.

An XML mapping cannot be exported if the mapped element’s relationship with other elements cannot be preserved. This relationship may not be preserved for the following reasons:

  • The schema definition of a mapped element is contained within a sequence with the following attributes:
    • The maxoccurs attribute is not equal to 1.
    • The sequence has more than one direct child element defined, or has another compositor as a direct child.
  • Nonrepeating sibling elements with the same repeating parent element are mapped to different XML tables.
  • Multiple repeating elements are mapped to the same XML table, and the repetition is not defined by an ancestor element.
  • Child elements from different parents are mapped to the same XML table.

Additionally, the XML mapping cannot be exported if it contains one of the following XML schema constructs:

  • List of lists    One list of items contains a second list of items.
  • Denormalized data    An XML table contains an element that has been defined in the schema to occur once (the maxoccurs attribute is set to 1). When you add such an element to an XML table, Excel fills the table column with multiple instances of the element.
  • Choice    A mapped element is part of a <choice> schema construct.

When I save my workbook as XML data, a message tells me that Excel cannot save the workbook because it does not contain any XML mappings.

Excel cannot save your workbook in the XML Data file unless you create one or more mapped ranges (mapped range: A range in an XML list that has been linked to an element in an XML map.).

If you added an XML map to your workbook, follow these steps to map XML elements to your worksheet:

  1. If the Developer tab is not available, do the following to display it:
  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.
  1. On the Developer tab, in the XML group, click Source.
    XML group in Ribbon
    The XML Source task pane is displayed.
  2. To map one or more elements to your worksheet, select the elements in the XML Source task pane. To select nonadjacent elements, click one element, and then hold down CTRL and click each element.
  3. Drag the selected elements to the worksheet location where you want them to appear.

If you haven't yet added an XML map to your workbook, or if your data is not contained within an XML table, save your workbook in the Excel Macro-Enabled Workbook file format (.xlsm).

Top of Page Top of Page

 
 
Applies to:
Excel 2007