XML Maps

XML maps are a way Excel represents xml schemas within a workbook
Excel uses maps as a way of binding the data from an xml file to cells and ranges on a worksheet.
You can only export data from Excel to XML by using an XML map.
If you have added an XML map to a worksheet, you can import data into that map at any time.

XML Schemas are copied into the workbook to create the map rather than referencing the schema as an external file.
Any subsequent changes to the schema do not affect the map.
You can only add and delete maps. You cannot update them.

Each XML map can only be used in a list once in your workbook.
There is a one-to-one relationship between the XML list, the XML Map and the data source.
If you want to import multiple XML files into a single workbook you will need to use a different map (and list) for each file.

Displaying XML maps

You can create and control the binding using the XML Source task pane.
You can view all the currently attached maps by using the drop-down on the XML source task pane.
Each map is displayed as a hierarchical list of elements

Creating an XML Map

There are two ways you can create an XML map:
1) Load an xml schema from an external file.
2) Rely on Excel to create a schema automatically by examining the structure of an imported xml file.

The schema can be copied from an XML schema file (xsd) or Excel can attempt to automatically generate one from an XML file (inferred schema)
XML maps are intended to be used with XML that is clearly structured.

When you create an XML map you use two types of XML elements:
Single, Non Repeating - These occur once in a given XML file.
Repeating - These can occur any number of times in a given XML file.

There are different icons to represent the two different types
SS

Limitations

1) Optional nodes are sometimes excluded (inferred schemas)
2) You can't update an existing map, you must delete and create a new one. Every time the xml schema changes the map must be recreated
3) You can't conditionally omit optional nodes
4) Excel doesn't automatically validate xml against schemas or display errors if the XML is invalid.

Excel adds an xml map for each unique xnl file you import into a workbook
If you import the same file twice, Excel simply reuses the xml map it already has.

Important Points

Items in an XML map can bind to only one location in a workbook.
If an item in an XML map is not bound to a cell or a list, its data will not be imported to (or exported from) the workbook.
If you include a non repeating item from an XML map in an Excel list, the data Excel saves on the worksheet becomes normalised and cannot be exported.

Denormalised means that non repeating elements appear multiple times on the worksheet.

Avoid lists of lists
Avoid normalised data

XML Map Properties

SS - XML Map Properties dialog box
(Data > XML > XML Map Properties)

Validate data against schema for import and export -
Save data source definition in workbook -
Adjust column widh -
Preserve column filter -
Preserve number formatting -
Overwrite existing data with new data -
Append new data to existing XML lists -

XML Maps in Templates

XML Maps become very useful when they are saved in Excel templates (.xlt)
That way the new workboosk based on these templates automatically contain the necessary XML map.
Storing an XML map in a template only make sense when you have finished developing and debugging it.