Exporting XML

Excel can export a table (or list) of data to an XML file.
When lists are exported you must select the corresponding map

There are two ways you can export mapped data to an XML file.
1) (Developer tab)(XML Group, Export)
2) (File > Save As, XML Data (*.xml)
The element names used in the workbook are always the ones exported even when you have different headings and labels.

Developer Tab > Export

Before you can export a range to XML you need to add an XML Map to the workbook.

The "Export XML" dialog box will appear if an XML table is not selected and the workbook contains more than one XML map.
In the large Export XML dialog box enter your filename an press Export.

Create an XML schema that outlines the structure of the data that you want to export

<?xml version="1.0" encoding="UTF-8" standalone="yes"/> 
<mydata xmlns:xsi="http://www.w3.org/2001/XML-Schema-instance">

Add the schema to the XML maps
drag and drop the elements from the task pane to your worksheet

File > Save As - XML Data (*.xml)

select Other Formats and type in the filename
In the save as type, select "XML Data"
You often see a prompt saying that saving as XML will mean that some features can no longer be used, but that is fine

XML Source Pane

The hierarchical display of elements is shown in the XML Source pane.
Elements that are required are indicated with a red asterisk in the top right corner.
If all the required elements are not mapped you will see the following message
SS - the map cannot be exported and requires elements that are not mapped.
Go back and map these by dragging them onto the worksheet.

Excel does not support recursive structures that are more than one level deep.
If you have a recursive structure with several layers you will see the following message
SS - The element is a recursive structure

Mixed content is not supported.
Mixed content is when you have an element that contains a child element as well as text outside of that element
SS ??

You cannot export data if the mapped elements are not able to keep their relationship.

Items that are not Exported

The following items are not exported
XML nodes not included in the list - if the map contains nodes that are not in the list only the nodes in the list will be xported.
Schema definitions
Processing instructions

In cases where a node contains a calculated value, you will need to perform the calculation in a non-mapped cell and then copy the value to the mapped cell before exporting.

(Data > XML > Export)
The "Export XML" dialog box appears
This option is only available when you workbook has at least one XML Map attached to it.

Once you have created a list containing XML data you can export data to a new XML file.
You can only export data using one XML map at a time
If a workbook contains more than one map you will be prompted to choose which map to use.

You cannot export denormalised data.
You cannot export lists of lists.

You cannot export all types of XML and Excel will omit any items that do not appear on the worksheet.

Excel can import XML that contains lists more than one level deep but it can export them.
It is possible to solve this problem by splitting the source XML file into smaller pieces (.ord).

Features that are not supported when exporting

List of Lists - only items one level deep can be exported
Denormalised data is not exported
Non repeating siblings - you cannot have any non repeating items
Repeating elements
Child elements from different parents
Choice - elements that are part of a schema choice construct can't be exported.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext