Export XML data

Admin
19 Dec, 2016

Export XML data

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.

Important:  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.

Here's how to export XML data from your worksheet:

  1. Click Developer > Export.

    If you don't see the Developer tab, see Show the Developer tab.

  2. 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.

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

  4. Click Export.

    Note: 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.

Here's how to export XML data from your worksheet if you are working with more than 65,536 rows:

  1. Subtract 65537 from the total number of rows in your file. This will give you x number of rows.

  2. Delete x rows from the beginning of the Excel worksheet.

  3. Export the worksheet as an XML data file.

  4. Close and reopen the Excel worksheet.

  5. Delete everything after the total x, and export as an XML data file.

    This procedure will give you remainder of the rows. At that point you will have two XML export files and combined they will total your original worksheet.

Save XML data in mapped cells to an XML data file

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.

  1. 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.

  2. In Excel 2010 and 2013, click File > Save As, and select the location where you want to save the file.

    In Excel 2007, click the Microsoft Office Button Office button image , point to the arrow next to Save As, and then click Other Formats.

  3. In the File name box, type a name for the XML data file.

  4. In the Save as type list, click XML Data, and click Save.

  5. If you get a message stating that saving the file as XML data may result in the loss of features, click Continue.

  6. 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.

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

  8. Click Export.

Note: 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.

Common issues with exporting XML data

When you export XML data, you may get messages like the ones below.

The XML Map can be exported but some required elements aren't mapped

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.

The XML Maps in your workbook can't be exported

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 the workbook because it doesn't have any mapped ranges

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:

  1. Click Developer > Source.

    XML commands on the Developer tab

    If you don't see the Developer tab, see Show the Developer tab.

  2. 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.

  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 isn't in an XML table, save your workbook in the Excel Macro-Enabled Workbook file format (.xlsm).

Show the Developer tab

If you don't see the Developer tab, do the following to display it:

  • In Excel 2013 and Excel 2010:

    1. Click File > Options.

    2. Click the Customize Ribbon category.

    3. Under Main Tabs, check the Developer box, and click OK.

  • In Excel 2007:

    1. Click the Microsoft Office Button Office button image > Excel Options.

    2. Click the Popular category.

    3. Under Top options for working with Excel, check the Show Developer tab in the Ribbon box, and click OK.

See Also

Over view of XML in Excel

Map XML elements to cells in an XML Map

Import XML data

Next Post Previous Post
No Comment
Add Comment
comment url