Amal Nagm

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 , and then click Excel Options.
   2. 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.

2. On the Developer tab, in the XML group, click Export.

   

   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.

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

Save XML data in mapped cells to an XML data file

1. Click the Microsoft Office Button , 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.

2. On the Microsoft Office Button , 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 then click Save.
5. 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.

6. In the large Export XML dialog box, in the File name box, type a name for the XML data file.
7. 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.

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 , and then click Excel Options.
   2. 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.
2. On the Developer tab, in the XML group, click Source.

   

   The XML Source task pane is displayed.
3. 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.
4. 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).