How to use the FILTERXML function
What is the FILTERXML function?
The FILTERXML function extracts specific values from XML content by using the given xpath. The function was introduced in 2013 and you need Excel 2013 or a newer version to use the FILTERXML function.
What is XML?
XML is an abbreviation for eXtensible Markup Language, it is a text format for storing and transporting data.
Introduction to XML - w3schools
What is a xpath?
XPath is a query language for selecting elements and attributes in XML data.
What is XPath? - w3schools
What's on this page
1. FILTERXML Function example
Formula in cell B3:
FILTERXML video
2. FILTERXML Function Syntax
FILTERXML(xml, xpath)
3. FILTERXML Function Arguments
xml | Required. A value in XML format. |
xpath | Required. A value in XPath format. |
4. How to extract XML attribute?
An XML element may contain an attribute, the following formula demonstrates how to extract a given attribute from XML data.
What is an XML attribute?
An XML attribute is a name-value pair that provides additional information about an XML element.
XML attribute- w3schools
What is an XML element?
An XML element is a basic unit of an XML document that consists of a start tag, an end tag, and the content in between.
XML Elements - w3schools
Formula in cell B6:
Cell B3 contains the following XML:
<cities><NorthAmerica city="Vancouver" country="Canada"/><NorthAmerica city="Seattle" country="Usa"/></cities>
The formula in cell B6 returns {"Vancouver";"Seattle"} spilled to cell B7.
5. How to import XML data?
- Select a destination cell.
- Go to tab "Developer" on the ribbon.
- Press with left mouse button on the "Import" button, a file dialog box appears.
- Press with mouse on an XML file to select it.
- Press with left mouse button on the "Open" button to import the file.
Here is what the example XML data looks like:
6. Put XML data in different columns based on XML tags?
The formula in cell B6 concatenates // with the value in cell B5 and then extracts the corresponding data.
Formula in cell B6:
The ampersand character lets you concatenate values in an Excel formula.
Cell B3 contains the following xml:
<countries><Europe>France</Europe><Asia>China</Asia><Europe>Spain</Europe><Asia>Japan</Asia><Asia>Thailand</Asia><Africa>Kenya</Africa></countries>
Cell B6 returns {"France";"Spain"}.
Explaining formula in cell B6
Step 1 - Concatenate column header to build xpath arg
"//"&B$5
becomes
"//"&"Europe"
and returns "//Europe"
Step 2 - Extract XML data
FILTERXML($B$3,"//"&B$5)
returns {"France";"Spain"}.
7. FILTERXML function not working
- The FILTERXML returns a #VALUE! error if the XML is not valid.
- The FILTERXML returns a #VALUE! error if the XML contains a namespace with a prefix that is invalid.
'FILTERXML' function examples
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
Picture above shows cheap chinese IoT modules: a temp sensor and an esp8266-01 (25 mm x 15 mm) Thingspeak […]
This article demonstrates formulas that rearrange values in a cell range to a single column. Table of Contents Rearrange cells […]
Functions in 'Web' category
The FILTERXML function function is one of 4 functions in the 'Web' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form