Microsoft Office Tutorials and References
In Depth Information
Parsing XML from the WEBSERVICE Function Using the FILTERXML Function
Caution
The next time you open this workbook, Excel warns you that the
WEBSERVICE does not automatically update. This ensures that you
can see the previous results even if the Internet is down.
The example returns weather data for a ZIP Code. You can see that they
are building a URL such as http://xml.weather.yahoo.com/forecastrss/
44685_f.xml to return data for ZIP Code 44685.
The existing formulas in the workbook use FILTERXML to return city and
region from yweather:location. Other formulas return temp and text from
yweather:condition. An array formula returns a column of day names from
yweather:forecast/@day. Another array formula returns a column of fore-
cast text from yweather:forecast/@text.
After exploring these formulas, paste the results from cell A2 into Notepad.
Find the yweather:forecast tag. You see there are fields called day, date,
low, high, text, and code. I have no idea what the code means, but the other
fields are self-explanatory (see Figure 12.49 ).
Figure 12.49.
Figure 12.49. Find the
Find the yweather:Forecast
yweather:Forecast tag to see what fields are avail-
tag to see what fields are avail-
able.
From this information, you can surmise that you can build a column of high
temperatures using =FILTERXML(A2,"//yweather:forecast/@high"). Select
five cells, type the formula, and press Ctrl+Shift+Enter. Excel returns the
high temperatures (see Figure 12.50 ) .
Search JabSto ::




Custom Search