How to use FILTERXML with local file?

LibreOffice Calc V6.0.4.2 on Win 10.

If I save the example xml content “books.xml” from this page to a local file I do not succeed with any formula I try using FILTERXML().

=FILTERXML("file:///C:/Users/MyID/folderpath/books.xml";"//book/year") or something using Xpath strings like /bookstore/book[1]/year only returns the #VALUE! error whether entered for array-evaluation or otherwise.

What’s wrong with my formulae?

I tried the same thing a while back but came to the conclusion given in @PYS’s answer.

Hi @Lupp

According to help first argument must be a string containing a valid XML stream.

I have no problem with:

  • A1 = samples.openweathermap.org/data/2.5/weather?q=London&mode=xml&appid=b6907d289e10d714a6e88b30761fae22
  • A2 =WEBSERVICE(A1)
  • A3 =FILTERXML($A$2;"//temperature/@min")

HTH - Regards

Thanks. (My concern was learning, not actually needing.) I also succeeded with getting a few data from the web. The help texts and the function wizard, however, describe the first parameter as XML Document. This was the reason for what I expected FILTERXML() should (also) work the way my posted example shows. After all there should some XML-parser functionality be expected.
(BTW: This is where I came from.)

After some additional research and testing I placed this post in the thread on forum.openoffice.org/en already mentioned in my comment on the answer by @PYS.

The demo attached there contains some statements about my experiences during the tests. It also contains a piece of simple Basic-code able to replace WEBSERVICE() as well for local XML-files as for files accessible via http://.

Another approach is to replace both WEBSERVICE and FILTERXML, as in this Python example.

import uno
import xml.etree.ElementTree as ET

def call_xpath_grab():
    titles = xpath_grab("C:/Users/MyID/folderpath/books.xml", "book/title")

def xpath_grab(filepath, xpath):
    # books.xml starts with empty space, which breaks the XML parser.
    # To fix this problem, this code reads the file into a string.
    with open(filepath, 'r') as myfile:
        data = myfile.read().replace('\n','')
    root = ET.fromstring(data)
    return root.findall(xpath)

It could be improved by adding HTTP handling as well.

Thanks Well, I won’t immediately start to use Python for some reason. Anyway: Does the Xpath engine you imported also refuse to accept empty lines? Am I in error concerning my assumption the emty lines (delimited in whatever way) of XML fikes should be ignored when the content ios interpreted?

Similar code can be written in Basic by using com.sun.star.ucb.SimpleFileAccess and com.sun.star.xml.sax.Parser as described at [Solved] Processing XML data with OpenOfice.org Basic (View topic) • Apache OpenOffice Community Forum. IMHO it’s much easier in Python though. Regarding empty lines, I do not know what is common practice, but the code in my answer handles them.