Programming Excel with VBA and .NET

Create an XML schema

Allowing Excel to infer a schema for an XML map is fine if the nodes don’t contain optional items or if the first occurrence of each node contains all of its possible children. Otherwise, Excel may omit items from the schema it creates and some nodes won’t appear in the XML map.

You can solve this problem by creating an XML schema and referencing that schema in the XML file you import. Excel copies the referenced XML schema into the XML map when the XML map is created.

Figure 15-16. A list with denormalized data

Figure 15-17. Put repeating and nonrepeating data items in separate lists to avoid denormalized data

Having an external XML schema is also useful for making changes to the XML map. As mentioned earlier, you can’t update an XML map inside of Excel; you can, however, modify the XML schema stored in the workbook by editing it outside of Excel. To edit an XML map schema:

1. In Excel, save the workbook as an XML spreadsheet.

2. Close the workbook in Excel.

3. Open the XML spreadsheet in an XML editor. It is a good idea to use a full-featured XML editor here because the schema generated by Excel does not include whitespace such as tabs and line feeds.

4. Edit the items in the map info/schema node as needed, or simply replace the entire schema node with the contents of your external ...