FILTERXML

The FILTERXML function returns specific data from XML content by using the specified XPath expression.

This function can be used to extract element values, attribute values, or filtered XML data from a valid XML-formatted string.

Syntax

=FILTERXML(xml, xpath)

Arguments

Parameter	Description
`xml`	A valid XML-formatted string or a cell reference that contains XML content.
`xpath`	A standard XPath-formatted string that specifies the data to extract from the XML content.

Remarks

FILTERXML returns the data that matches the specified XPath expression.
If the XML string is invalid, FILTERXML returns the #VALUE! error value.
If the XML string contains a namespace with an invalid prefix, FILTERXML returns the #VALUE! error value.
If the XPath expression is invalid or no matching data is found, FILTERXML returns the #VALUE! error value.
If the result contains multiple values, the result follows the dynamic array or array formula behavior in SpreadJS.

Examples

Extract Element Values from XML

Suppose cell A1 contains the following XML string:

<bookstore>
  <book><title>Harry Potter</title><price>29.99</price></book>
  <book><title>Learning XML</title><price>39.95</price></book>
  <book><title>XQuery Kick Start</title><price>49.99</price></book>
</bookstore>

Formula	Result	Description
`=FILTERXML(A1,"//title")`	`{"Harry Potter"; "Learning XML"; "XQuery Kick Start"}`	Returns all `<title>` element values.
`=FILTERXML(A1,"//book[1]/title")`	`Harry Potter`	Returns the title of the first book.
`=FILTERXML(A1,"//book[last()]/title")`	`XQuery Kick Start`	Returns the title of the last book.
`=FILTERXML(A1,"//book/price")`	`{29.99; 39.95; 49.99}`	Returns all `<price>` element values.
`=FILTERXML(A1,"//book[price>30]/title")`	`{"Learning XML"; "XQuery Kick Start"}`	Returns titles of books with a price greater than 30.

Extract Data Using Attributes

Suppose cell A1 contains the following XML string:

<catalog>
  <product id="1" category="electronics">Laptop</product>
  <product id="2" category="electronics">Phone</product>
  <product id="3" category="clothing">T-Shirt</product>
</catalog>

Formula	Result	Description
`=FILTERXML(A1,"//product/@id")`	`{1; 2; 3}`	Returns all `id` attribute values.
`=FILTERXML(A1,"//product[@category='electronics']")`	`{"Laptop"; "Phone"}`	Returns products in the `electronics` category.
`=FILTERXML(A1,"//product[@id='2']")`	`Phone`	Returns the product with `id="2"`.

Extract Text with XPath Functions

Suppose cell A1 contains the following XML string:

<employees>
  <employee><name>Alice</name><dept>Engineering</dept></employee>
  <employee><name>Bob</name><dept>Marketing</dept></employee>
  <employee><name>Charlie</name><dept>Engineering</dept></employee>
</employees>

Formula	Result	Description
`=FILTERXML(A1,"//employee[contains(name,'li')]/name")`	`{"Alice"; "Charlie"}`	Returns names that contain `li`.

Use FILTERXML with WEBSERVICE

You can use FILTERXML with the WEBSERVICE function to extract data from XML returned by a URL.

=FILTERXML(WEBSERVICE("https://example.com/data.xml"),"//item/name")

This formula first uses WEBSERVICE to download XML data from the specified URL, and then uses FILTERXML to extract all <name> elements under <item>.

If multiple matching values are found, the result follows the dynamic array or array formula behavior in SpreadJS.

Notes

FILTERXML uses XPath expressions to locate data in XML content.
When the result contains multiple values, the values can spill into multiple cells depending on the formula mode and dynamic array behavior.
SpreadJS does not have the same 32,767-character cell string limitation as Excel. In scenarios where Excel truncates long XML strings or returns #VALUE! for long WEBSERVICE responses, SpreadJS may return a valid result if the XML content is complete and valid.