Microsoft Office Tutorials and References
In Depth Information
Parsing XML from the WEBSERVICE Function Using the FILTERXML Function
There are two throttles on the WEBSERVICE function. First, the owner
of the API limits how many times you can call the API per hour. Se-
cond, Microsoft has imposed throttling limits to prevent you from us-
ing Excel in a Denial of Service attack.
Parsing XML from the
Parsing XML from the WEBSERVICE
The XPath query language lets you query XML results such as the data
sites that help you get up to speed with XPath. The following examples also
WEBSERVICE Function Using the
Function Using the FILTERXML
Returns specific data from the XML content by using the specified XPath.
This function requires two arguments:
xml — A string in valid XML format. If xmlis not valid, FILTERXML
returns the #VALUE! error. If xmlcontains a namespace with a prefix
that is not valid, FILTERXML returns the #VALUE! error.
xpath — A string in standard XPath format.
In the example in Figure 12.46 , the web service is returning the first 15 res-
ults. A single piece of XML can contain several children nodes. The Name and
Title fields appear 15 times each in the results in C7. To break out the 15 res-
ults, follow these steps:
11. To simplify the formulas, name the cell containing the XML. Select
cell C7. Click in the Name box to the left of the formula bar. Type
Result and press Enter.
22. Select a single column of cells that is 15 cells tall.
33. Type the formula =FILTERXML(Result, "//title")
=FILTERXML(Result, "//title"). Instead of pressing
Enter, hold down Ctrl and Shift while pressing Enter. Excel parses
out the 15 tweets.