7.5. Mapping from an existing XML document In our previous example, we started with an Excel worksheet and mapped a schema to it. There is another alternative: You can open an XML document in Excel and it will bring up a blank worksheet. You can then map to that using either the schema of the XML document, or an inferred schema. The advantage to this approach is that you can refresh the data during the mapping process to ensure that it is being imported correctly from the XML document. The disadvantage is that you have to start from scratch with the formatting and layout of the worksheet. 7.5.1 Using a schema Some XML documents, such as the expenses.xml document, shown in Example 7-1, use the xsi:schemaLocation attribute to specify the location of the schema. When you open such a document, Excel knows about the schema and it automatically brings it up in the XML Source task pane. To open an XML document with a schema in Excel: - 1. Click Open on the File menu.
- 2. Select XML Files (*.xml) from the Files of type: list.
- 3. Select the expenses.xml file and click Open. You are given a dialog with three options, as shown in Figure 7-7.
Figure 7-7. The Open XML dialog 
- 4. Choose the third option, Use the XML Source task pane. The first two options bring in the XML data in a less useful way.
The schema structure automatically appears. The worksheet is a completely blank slate, and you can begin mapping to it. For non-repeating element types, you can use the smart tag that appears to the right of a recently mapped cell to add a label for the data, namely the element-type name. For repeating element types, the list column headers are automatically populated with the element-type names. You can map a parent element type and all its children in one step by selecting the parent and dragging it to the worksheet. For example, select expense and drag it to cell B2. This will create a list range starting at B2 which has a column for each child of expense. You can remove any unwanted columns from the list range by selecting a column (for example phone), right-clicking it, and clicking Remove element. Once you have mapped some element types, you can populate the data to ensure that the map is correct. To do this, position your cursor in a mapped cell and click Refresh on the XML submenu. You can refresh as often as you like during the mapping process. 7.5.2 Inferring a schema If you don't have a schema, Excel will infer a schema from an XML document. If you attempt to open an XML document that does not make use of the xsi:schemaLocation attribute to indicate a schema, Excel will prompt you with a dialog saying "The specified XML source does not refer to a schema. Excel will create a schema based on the XML Source data." It will then infer a schema from the XML document. We do not recommend this approach. The problem is that Excel may not get the schema 100% right, because it has limited information. That is understandable, but there is no way to change the schema once it is inferred. It is embedded in the Excel workbook and you cannot edit it. One of the most common problems is that the schema inference tool assumes that some non-repeating element types are actually repeating. This wreaks havoc on the ability to map them to the worksheet. To avoid writing a schema from scratch, you can use a separate tool to infer a schema from an XML document. Microsoft provides a free schema inference tool at: http//apps.gotdotnet.com/xmltools/xsdinference
Once you have generated a schema, you will have the ability to fine-tune it before you begin mapping. | 
