Case Study Using XML Data from Amazoncom

The promise is that all sorts of XML data will soon be available. One high-profile example available now is Amazon.com. They now offer a wide variety of queryable data that is returned in XML format.

It is possible to use VBA to query data from Amazon and have the results returned to Excel.

Amazon.com offers a software development kit for download from http://amazon.com/webservices.To get the kit,follow these steps.

1. Follow the link to download the free developer's kit.

2. Click the Download Now button.

3. Unzip the kit.zip file to your hard drive.

4. The development kit describes two schemas:a lite schema and a heavy schema. In ReadMeFirst.txt,there will be a link to the heavy XSD.At press time,this link was located at http://xml.amazon.com/schemas3/ dev-heavy.xsd.

5. Open that file in a browser and save it to your hard drive.

Starting with a blank Excel workbook, I first attach the schema to the workbook:

1. From the menu,select Data,XML,XML Source.

2. In the XML Source pane,click the XML Maps button.

3. In the XML Maps Dialog,click the Add button.

4. In the File Name box,type the URL for the Amazon heavy XSD file:http://xmi.amazon.com/schemas3/ dev-heavy.xsd.Select Open.

5. In the Multiple Roots dialog, select the ProductInfo as the root note of interest.

6. Click OK to close the XML Maps dialog.

All the fields available in the Amazon.com ProductInfo schema now appear in the XML Source pane. Drag any fields of interest to your spreadsheets shown in Figure 15.7.

Figure 15.7

After the XSD Schema is attached to the work-book,you can drag fields of interest from the XML Source pane to the spreadsheet.

Figure 15.7

After the XSD Schema is attached to the work-book,you can drag fields of interest from the XML Source pane to the spreadsheet.

+1 0

Post a comment