excel: Cannot save or export xml data. The xml map in this workbook are not exportable

Question

I want to export data back to an XML file, after some modification to data I imported from some other XML file (those first line variables never change).

When I click Developer >> export

Excel shows:

cannot save or export XML data. The XML maps in this workbook are not exportable.

This happes even when I do nothing to modify the data.

I watched several export tutorials on YouTube, but none cover my problem.

Answer 1

The usual reason is the Excel doesn't understand very complicated XML.

From office.microsoft.com:

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

Answer 2

I had a similar issue. It was a simple sheet and contained just 8 columns but the XML export kept throwing this error. I found that if the order in which the elements appeared in the XML schema did not match the order in which the columns appeared in the sheet, I got this error. I rearranged the columns in my sheet to be the same as the order of elements in the schema & the export became successful.

Answer 3

In my case i wanted to export a simple table as an xml file without a prior existing xsd or xml file. So i did this

1. Create an xsd file (see sample below)
2. Import this xsd into excel
3. Relate nodes in the xml structure to a column (see screen below)
4. Export sheet as a xml file

Step 1 create an xsd file

I wrote the following xsd in an editor and saved it to a file

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="Root">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element minOccurs="0" maxOccurs="unbounded" name="Sample_XML_Export">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="Foo" type="xsd:int"/>
              <xsd:element name="Bar" type="xsd:string"/>
              <xsd:element name="Baz" type="xsd:int" />
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Step 2 Import xsd file

This xsd file must be imported into excel. You need to activate the developer tab in Excel. On this tab you can import from XML source (see button "XML...").

After you imported the xsd file you will see a tree structure (see folder Root and Sample_XML_Export).

Step 3 map xml nodes to columns

Now you need to relate a node below Sample_XML_Export to an excel-column. Select a node and right click it to map this node to a column

Now select the column you want to map / to relate to the node by clicking on the column header.

Step 4 save as xml

Under File / Save as or Export you can save the xls file as an xml file. This is the exported xml data

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Root>
    <Sample_XML_Export>
        <Foo>1234</Foo>
        <Bar>122</Bar>
        <Baz>12</Baz>
    </Sample_XML_Export>
    <Sample_XML_Export>
        <Foo>4567</Foo>
        <Bar>144</Bar>
        <Baz>13</Baz>
    </Sample_XML_Export>
    <Sample_XML_Export>
        <Foo>7890</Foo>
        <Bar>187</Bar>
        <Baz>14</Baz>
    </Sample_XML_Export>
</Root>