After you've 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.
:
-
Before exporting XML data, you will need to enable the Developer tab on the ribbon. See Show the Developer tab.
-
When creating XML Maps and exporting data in Excel to XML data files, there's a limit on the number of rows that can be exported. Export to XML from Excel will save a maximum of 65,536 rows. If your file has more than 65,536 rows, Excel will export the first (#ROWS mod 65537) rows. For example, if your worksheet has 70,000 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 65,536 and then export again which will keep the mappings but lose the data at the end of the file.
Export XML data (with no more than 65,536 rows)
-
Click Developer > Export.
If you don't see the Developer tab, see Show the Developer tab.
-
If a small Export XML dialog box appears, click the XML Map you want to use, and click OK.
This dialog box only appears if an XML table isn't selected and if the workbook has more than one XML Map.
-
In the large Export XML dialog box, in the File name box, type a name for the XML data file.
-
Click Export.
: If your worksheet has headings or labels that are different from the XML element names in the XML Map, Excel will use the XML element names when you export or save XML data from the worksheet.
Export XML data (for more than 65,536 rows)
-
Subtract 65537 from the total number of rows in your file. This will give you x number of rows.
-
Delete x rows from the beginning of the Excel worksheet.
-
Export the worksheet as an XML data file (see the previous section for the procedure).
-
Click Close but do not save the sheet. Then reopen the Excel worksheet.
-
Delete everything after the total x, and then export as an XML data file (see the previous section for the procedure).
This procedure will give you remainder of the rows. At that point, you will have two XML export files, which you can combine to create a duplicate of your original worksheet.
For backward compatibility with earlier XML functionality, you might want to save the file to an XML data file (.xml) instead of using the Export command.
-
Press Ctrl+S to save your file.
This step ensures that any changes you made to your workbook won't be lost when the workbook is saved as an XML data file.
-
Click File > Save As, and select the location where you want to save the file.
In Excel 2007 only, click the Microsoft Office Button
-
In the File name box, type a name for the XML data file.
-
In the Save as type list, click XML Data, and click Save.
-
If you get a message stating that saving the file as XML data may result in the loss of features, click Continue.
-
If a small Export XML dialog box appears, click the XML Map you want to use, and click OK.
This dialog box only appears if an XML table isn't selected and the workbook has more than one XML Map.
-
In the large Export XML dialog box, in the File name box, type a name for the XML data file.
-
Click Export.
: If your worksheet has headings or labels that are different from the XML element names in the XML Map, Excel will use the XML element names when you export or save XML data from the worksheet.
When you export XML data, you may get messages like the ones below.
There may be several reasons for this message:
-
The XML Map that is associated with this XML table has one or more required elements that aren't mapped to the XML table.
The hierarchical list of elements in the XML source task pane indicates required elements by placing a 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 doesn't support recursive structures that are more than one level deep and therefore can't map all of the elements.
-
The XML table contains mixed content.
Mixed content occurs when an element has a child element and simple text outside of a child element. One common case is where formatting tags (such as the bold tags) are 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, isn't available when the data is exported, and therefore can't be round-tripped.
An XML Map can't be exported if the mapped element’s relationship with other elements can't 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 isn't 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 isn't defined by an ancestor element.
-
Child elements from different parents are mapped to the same XML table.
Additionally, the XML Map can't be exported if it has one of the following XML schema constructs:
-
List of lists One list of items has a second list of items.
-
Denormalized data An XML table has 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.
Excel can't save your workbook in the XML Data file unless you create one or more mapped ranges.
If you added an XML Map to your workbook, follow these steps to map XML elements to your worksheet:
-
Click Developer > Source.
If you don't see the Developer tab, see Show the Developer tab.
-
In the XML Source task pane, select the elements you want to map to your worksheet.
To select nonadjacent elements, click one element, hold down CTRL, and then click each element.
-
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 isn't in an XML table, save your workbook in the Excel Macro-Enabled Workbook file format (.xlsm).
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
