# FILTERXML

## Content

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

```javascript
=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:

```xml
<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:

```xml
<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:

```xml
<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.

```javascript
=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.