How To Use Filterxml Function In Excel

The FILTERXML part is a Web formula that tin can render specific data from an XML content, using a given XPath. This ability makes the function suitable for use in conjunction with the WEBSERVICE role. The FILTERXML function is available for 2013 and later versions of Excel on the Windows platform. In this guide, nosotros're going to show you lot how to use the FILTERXML role, and also go over some tips and error handling methods.

---

Supported versions

• Excel 2013 and later versions for Windows. Not available in Excel Online and Excel for Mac.

---

FILTERXML Role Syntax

---

Arguments

xml	An XML string.
xpath	A string specifying XPath.

---

Examples

FILTERXML Function'south Unmarried Value Render

The FILTERXML function requires ii arguments: The first is the XML text, and the second is the query linguistic communication called XPath. The XPath stands for XML Path Language, and it uses a "path-like" syntax to place the where the data is to be placed. "Path-like" here means a string that starts with double slashes, and contains a unmarried slash for each binder (tag operator in XML).

Here is our sample information which contains information about stock quote for MSFT:

<QuoteApiModel>     <Data>         <Status>SUCCESS</Status>         <Name>Microsoft Corp</Name>         <Symbol>MSFT</Symbol>         <LastPrice>137.08</LastPrice>         <Alter>-1.82</Change>         <ChangePercent>-1.3102951764</ChangePercent>         <Timestamp>Tue Jul xvi 00:00:00 UTC-04:00 2019</Timestamp>         <MarketCap>1050419091440</MarketCap>         <Book>22726128</Volume>         <ChangeYTD>101.57</ChangeYTD>         <ChangePercentYTD>34.9611105641</ChangePercentYTD>         <High>139.05</High>         <Low>136.52</Low>         <Open>138.96</Open>     </Information> </QuoteApiModel>        

If we need to take hold of the Name value from this XML, we need to employ an XPath string similar this:

//QuoteApiModel/Information/Name

Let's encounter how information technology works in Excel:

=FILTERXML(B1,"//QuoteApiModel/Data/Name")

FILTERXML Function'south Array Return

An alternative way to use the FILTERXML office is using it equally an array formula to return all values with the same tags. This usage comes in handy if the return XML contains similar data, such as the dishes in a breakfast bill of fare.

Here is some other XML string that lists the stock values by a specified keyword. We used the "software" keyword in our case. Since the stock market doesn't consist of a unmarried visitor, we once again go a long XML cord with the aforementioned tags used more than once, signifying the same kind of information with different values.

<LookupResultList>     <LookupResult>         <Symbol>CHKP</Symbol>         <Name>Check Point Software Technologies Ltd</Name>         <Commutation>NASDAQ</Exchange>     </LookupResult>     <LookupResult>         <Symbol>PRGS</Symbol>         <Name>Progress Software Corp</Name>         <Exchange>NASDAQ</Substitution>     </LookupResult>     <LookupResult>         <Symbol>TTWO</Symbol>         <Name>Take-Ii Interactive Software Inc</Name>         <Exchange>NASDAQ</Exchange>     </LookupResult>     <LookupResult>         <Symbol>AMSWA</Symbol>         <Name>American Software Inc</Proper noun>         <Exchange>NASDAQ</Exchange>     </LookupResult>     <LookupResult>         <Symbol>GWRE</Symbol>         <Name>Guidewire Software Inc</Proper name>         <Exchange>NYSE</Exchange>     </LookupResult>     <LookupResult>         <Symbol>DATA</Symbol>         <Name>Tableau Software Inc</Name>         <Exchange>NYSE</Exchange>     </LookupResult>     <LookupResult>         <Symbol>PAYC</Symbol>         <Name>Paycom Software Inc</Name>         <Substitution>NYSE</Exchange>     </LookupResult>     <LookupResult>         <Symbol>CYBR</Symbol>         <Name>Cyberark Software Ltd</Name>         <Commutation>NASDAQ</Commutation>     </LookupResult>     <LookupResult>         <Symbol>COUP</Symbol>         <Name>Coupa Software Inc</Proper noun>         <Exchange>NASDAQ</Exchange>     </LookupResult>     <LookupResult>         <Symbol>PVTL</Symbol>         <Name>Pivotal Software Inc</Name>         <Exchange>NYSE</Commutation>     </LookupResult> </LookupResultList>        

To become the symbol names we need to use a path similar below.

//LookupResultList/LookupResult/Symbol

If y'all endeavor creating this path the aforementioned way as before, yous will merely get the get-go symbol of the stocks. The reason backside this is that the function tin can get all symbols as an array, simply can only evidence the first one, if information technology is non set as an array formula. To catechumen a regular formula into an array equivalent tin exist done similar this:

1. Begin past selecting a range that contains the same number of cells every bit the return values. For example, E3:E12
2. Type in your formula (i.e. =FILTERXML(E1,E2) ). Cell E2 contains the aforementioned XPath string: //LookupResultList/LookupResult/Symbol
3. Press the Ctrl + Shift + Enter primal combination instead of Enter . You practise non need this stride if you have the current version of Excel 365. Enter will be enough to encounter all values. For more than information, delight visit our article about Excel's new concept: Dynamic Arrays

All that'southward left to exercise is getting all the symbols together.

Download Workbook

---

Tips

• Use the FILTERXML to parse the XML returns from the WEBSERVICE function.
• The FILTERXML function may announced in the Excel for Mac function gallery, merely it doesn't piece of work on Mac, considering of the role's dependencies on Windows operating organization features.

Source: https://www.spreadsheetweb.com/excel-filterxml-function/

Share this post