Add an XML Map to an Excel Workbook

There are 2 sources for XML maps I know of:

1.You have a copy of the XML data you wish to map (XML file)
2.You know the location of the XML’s schema (XSD file)

In both cases, it’s simply a matter of pointing Excel to either location. In the first case Excel guesses at the Map, in the latter it uses the data in the Schema to create the map.

For this example lets assume we want to Create a Map for the XML data returned from the Yahoo Web Search Service.

This is a good example to demonstrate each method because they have a sample of the XML data available (Update: Unfortunately the link http://api.search.yahoo.com/WebSearchService/V1/webSearch?appid=YahooDemo&query=madonna&results=2  no longer works, so the sample XML data is no longer available), and they also have the Schema available, and both work to create the Map.

Note: Keep in mind the XML or XSD files need not be at a url address, they can be saved on your local drive.

Create a Map in Excel
1. On the main menu goto Data->XML->XML Source
2. Click the XML Maps Button

3. In the newly opened dialog box click the Add button

4. Enter the location of the XSD or XML file you wish to Map

Note: If you are trying to paste the URL into the File Name box, you can’t right click in the box, you must use the paste shortcut (Ctrl-V)

5. Click the OPEN button.

That’s it, you should have an XML Map in your workbook based on the XML or XSD file you designated. Here’s what the map I just added looks like: