Excel 2007 - How to connect to and import data from an XML Web Service

Many organizations have adopted a Service-Oriented Architecture (SOA) as the architecture of choice. SOA allows a company to expose its services and data in a manner that is compliant with industry standards. SOA is the buzzword these days. Down the wire of SOA is XML data that is exchanged between entities.

Microsoft Office Excel 2007 has built-in features that allow a user to connect to and consume XML data. Such data can be stored in an XML file on the local hard drive, shared in a portal or returned from an XML Web Service.

If you’d like to use Excel to import XML data from a Web Service, follow these step-by-step guidelines. For the sake of simplicity, this document will be using a web service that is available on the Internet called Teach a Techie (http://teachatechie.com). This web service has a function named GetLocation and the URL we’ll be using is http://teachatechie.com/GJTTWebServices/ZipCode.asmx/GetLocation?ZipCode=11413

  1. Start Excel 2007.

  2. Select the Data Tab:

  3. Click on the Get External Data button, then select From Other Sources, then select From XML Data Import:

  4. Enter the URL above into the File Name box, then click on the Open button:

  5. Wait for Excel to open the XML schema and the data. In our example, the web service does not have an associated XML Schema. Excel will prompt you to create one for you. Click on OK to accept this action.

  6. Excel will prompt you for the cell where you want to import the data. Select the location and press on OK.

  7. Now the data is imported into Excel. You can utilize all the features of Excel to analyze and report on your data.

Happy analysis!