Ask Your Question
1

How to use FILTERXML with local file?

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

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

Comments

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 +0200 )edit

3 Answers

Sort by » oldest newest most voted
1

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

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

HTH - Regards

edit flag offensive delete link more

Comments

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 +0200 )edit
0

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

Lupp gravatar image

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

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 @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
0

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

Jim K gravatar image

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

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.

edit flag offensive delete link more

Comments

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 +0200 )edit

Similar code can be written in Basic by using com.sun.star.ucb.SimpleFileAccess and com.sun.star.xml.sax.Parseras described at https://forum.openoffice.org/en/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.

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

Question Tools

1 follower

Stats

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

Seen: 413 times

Last updated: Jun 26 '18