Ask Your Question

How to use FILTERXML with local file?

asked 2018-06-24 11:36:36 +0100

Lupp gravatar image

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?

edit retag flag offensive close merge delete


I tried the same thing a while back but came to the conclusion given in @pierre-yves samyn's answer.

Jim K gravatar imageJim K ( 2018-06-25 22:31:09 +0100 )edit

3 Answers

Sort by » oldest newest most voted

answered 2018-06-24 14:07:39 +0100

pierre-yves samyn gravatar image

Hi @Lupp

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

I have no problem with:

  • A1 =
  • A3 =FILTERXML($A$2;"//temperature/@min")

HTH - Regards

edit flag offensive delete link more


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.)

Lupp gravatar imageLupp ( 2018-06-24 14:32:40 +0100 )edit

answered 2018-06-26 03:09:53 +0100

Lupp gravatar image

updated 2018-06-26 03:10:54 +0100

After some additional research and testing I placed this post in the thread on already mentioned in my comment on the answer by @pierre-yves samyn.

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://.

edit flag offensive delete link more

answered 2018-06-26 16:00:22 +0100

Jim K gravatar image

updated 2018-06-26 16:14:51 +0100

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 ='\n','')
    root = ET.fromstring(data)
    return root.findall(xpath)

It could be improved by adding HTTP handling as well.

edit flag offensive delete link more


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?

Lupp gravatar imageLupp ( 2018-06-26 16:14:23 +0100 )edit

Similar code can be written in Basic by using and described at 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.

Jim K gravatar imageJim K ( 2018-06-26 16:21:45 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-06-24 11:36:36 +0100

Seen: 942 times

Last updated: Jun 26 '18