The FILTERXML function returns specific data from XML content by using the specified xpath.
Notes:
-
The FILTERXML function is not available in Excel for the web and Excel for Mac.
-
This function may appear in the function gallery in Excel for Mac, but it relies on features of the Windows operating system, so it will not return results on Mac.
Syntax
FILTERXML(xml, xpath)
The FILTERXML function syntax has the following arguments.
|
Argument |
Description |
|---|---|
|
xml Required |
A string in valid XML format |
|
xpath Required |
A string in standard XPath format |
Notes:
-
If xml is not valid, FILTERXML returns the #VALUE! error value.
-
If xml contains a namespace with a prefix that is not valid, FILTERXML returns the #VALUE! error value.
Examples
-
This example uses the FILTERXML function on XML data returned in cell B2, which is provided by the results of the WEBSERVICE function on the web query in cell B1.
The data from FILTERXML is returned in cells B3:B5 and C3:C5, and shows the three most recently updated query results on Wikipedia and the time of their updates (in UTC, "Coordinated Universal Time").
Cells B3:B5 contain the formula =FILTERXML(B3,"//rc/@title").
Cells C3:C5 contain the formula =FILTERXML(B3,"//rc/@timestamp").
Note: If you have a current version of Microsoft 365, then you can simply enter the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by first selecting the output range, entering the formula in the top-left-cell of the output range, and then pressing CTRL+SHIFT+ENTER to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you. For more information on array formulas, see Guidelines and examples of array formulas.
-
You can also use FILTERXML in conjunction with the ENCODEURL and WEBSERVICE functions. For example, you can use the markitondemand.com api service to return information about a company stock:
=WEBSERVICE("http://dev.markitondemand.com/MODApis/Api/Quote/xml?symbol="&ENCODEURL(C2))
Where you have a stock ticker in cell C2.
You can then use the FILTERXML function to extract specific data about that stock. For example, to get the Last Price of the stock ticker in cell C2, you can use:
=FILTERXML(WEBSERVICE("http://dev.markitondemand.com/MODApis/Api/Quote/xml?symbol="&ENCODEURL(C2)),"//QuoteApiModel/Data/LastPrice")
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
