Excel: Filter XML Using XPATH

Excel plays really nicely with XML (as it ought to, because XML is super cool). You might be pleased to learn that you can use XPATH expressions with a handy function called FILTERXML. It works fine for XML dumped straight into a cell, or you can apply it to a WEBSERVICE call. The function has only two arguments: the first is your XML and the second is your XPATH expression.

Try this very contrived example I’ve concocted to see it in action. Here’s a bit of XML you can paste into a fresh worksheet in cell A1:

<item>apple

banana</item>peach</example>

Now enter this formula in any other cell, where I’m using XPATH to pull out the second item:

=FILTERXML(A1,"//item[2]")

That’ll give you banana!

Categories: functions, SQL, SQL Server