Microsoft Office 2016: The Complete Guide (2015)

EXPORT XML DATA FROM EXCEL 2016

After importing XML data, mapping the data to cells and have made modifications, it is no surprise that you may feel the need to export or save the data to an XML file.

TIP: - When building a XML maps and exporting data in Excel to XML files, there is a maximum of rows that are able to be transported. The number of rows are 65,536. If, however, your file exceeds this amount then here are some tips to remedy this which includes:

Export the data using the .xlsx file format

Save the file as a XML Spreadsheet (.xml), that can result in the mappings being lost.

Erase all rows after 65,536 after which you should export again to preserve the mappings but get rid of the rest of data.

Sending XML Data from a Worksheet of No More than 65,536 Rows.

On the [Developer tab] inside the XML group, select Export. If a Developer Tab is not visible, then see Show the Developer Tab link.

In case a small export XML dialog box appears, select the XML map you would like to employ and select OK.

Please note that this dialog box is only visible if an XML table isn’t selected and if the workbook contains two or more XML group.

In the large Export XML dialog box, inside the File Name box, type a title that you want to be attributed to the XML data file.

Select export.

NOTE: - In case your worksheet’s headings and labels show disparity between those of the XML elements names in the XML map, by default Excel will apply the XML element names when you export or save XML data from the worksheet.

Export XML Data from a Worksheet with More Than 65,536 Rows.

Subtract 65,537 from the total number of rows in your file. This will give you the total of the remainder of rows and this value will be denoted by the letter ‘x’.

Get rid of ‘x’ rows from the beginning of the Excel worksheet.

Send the worksheet on an XML data file.

Select Close, and then reopen the Excel worksheet.

Erase, everything after the total ‘x’ and then export an XML data file.

This method results in producing the remainder of the rows. Where two XML exports are available which can be amalgamated to create a replica of the original worksheet.

Save XML Data in Mapped Cells to an XML Data File.

For backward compatibility with earlier XML functionality it is advised that you save the file to an XML data file (.xml) as opposed to using the [Export] command. To achieve this, the following steps are required:

Press CTRL+S to save your file. This is important as it prevents any modifications that were made to the workbook is saved as an XML data file.

Select [File] then [Save As], then choose and select the location where you want to sane the file.

Inside the File Name box, type a name for the XML data file.

In the [Save as Type] list, select XML Data then select [Save].

If it is that you should get a message saying that saving the file as a XML data may result in the loss of features, select [Continue]

In the event that a small Export XML Dialog box should appear, select the XML group that you would like to employ and then select OK.

This Dialog Box only appears if an XML table isn’t selected and if the workbook has more than one XML map.

Inside the large Export XML Dialog box, in the File name box and type the label for the XML data file.

Select [Export]

NOTE: In the event that your worksheets’ headings show disparity between those of the element names in the XML map. Once again, by default, Excel will only employ the XML element names when you export or save XML data from the worksheet.

Common Issues with Exporting XML Data

Whenever XML data is exported, it is likely that you may receive something to the essence of the following messages:

“The XML map can be exported but some required elements are not mapped.”

There may be several reasons as to why you may receive this message:

The XML map that is related to XML table has one or more essential features that are not mapped to the XML table.

The hierarchical list of elements in the XML source task pane denotes the requisite features by putting 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 be displayed.

The element is a recursive structure- A very conventional example of a recursive structure is a hierarchy of employees and managers in which the same XML elements are nested several levels. Even if you have mapped out all the elements in the XML Source Task Plane. Excel does not support recursive structures exceeding on level deep and as such it can’t map all of the elements.

The XML table contains Mixed Content- An element that has a child element and simple text outside of a child element give rise to mixed content. A popular scenario is where formatting tags (such as bold tags) are used to markup data within an element. Although the child element (if supported by Excel) can be shown, the textual content will be lost when the data is imported and subsequently cannot be round-tripped.

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 contains more than one direct and defined child element or has a different compositor as a direct child.

Unique sibling elements with the same repeating parent element are mapped to different XML tables.

Various repeating elements are mapped to the same XML table and the reiteration isn’t defined by an ancestor element.

Child elements different parents are mapped to the same XML table.

Furthermore, the XML map can’t be exported if it has one of the following XML schema constructs:

Lists of list- one list of items has a second list of items.

De-normalized data- an XML table has an element that has been defined in the schema to happen only once. Whenever this particular element is added to an XML table Excel fills the table column with multiple instances of the element.

Choice- a mapped element is a feature of a choice> schema construct.