Programming Excel with VBA and .NET

Use Schemas with XML Maps

When Excel imports an XML file that does not reference an XML schema, it infers a schema from the nodes in the XML file. The preceding section explains one of the limitations of inferring a schema—optional nodes are sometimes omitted from the resulting XML map.

Another solution to this problem is to include a schema with your XML file. For example, the following XML fragment references a schema for the SimpleOrder.xml file:

<Orders xmlns="http://www.mstrainingkits.com"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.mstrainingkits.com SimpleOrder.xsd">

When Excel imports an XML file that references a schema, it copies that schema into the workbook. If the XML is valid according to that schema, you can drag nodes from the XML map onto the worksheet to create lists and import data as shown previously.

If the XML is not valid for the schema, however, no data will appear in the lists you create. Excel does not automatically validate XML against schemas or display errors if the XML is invalid. To validate XML within Excel:

1. From the Data menu, choose XML then choose XML Map Properties. Excel displays the XML Map Properties dialog box (Figure 15-11).

2. Select “Validate data against schema for import and export” and click OK to close the dialog box.

Figure 15-11. Validating XML

Now, Excel will display an error if the XML doesn’t conform ...