Bug 88257 - XMLpath in FIlterxml does not work with arrayfunction
Summary: XMLpath in FIlterxml does not work with arrayfunction
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3.4.1 release
Hardware: All All
: medium enhancement
Assignee: Eike Rathke
URL:
Whiteboard: target:5.3.0 target:5.2.2
Keywords:
Depends on:
Blocks:
 
Reported: 2015-01-09 22:36 UTC by Oyvind
Modified: 2016-08-30 14:15 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
example usage FILTERXML workbook excel (9.11 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-01-09 22:36 UTC, Oyvind
Details
Sample file (19.64 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2015-01-10 02:56 UTC, m_a_riosv
Details
New sample (16.37 KB, application/vnd.oasis.opendocument.text)
2016-04-06 22:32 UTC, m_a_riosv
Details
A new example (17.84 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-04-19 06:42 UTC, mahfiaz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Oyvind 2015-01-09 22:36:57 UTC
Created attachment 112037 [details]
example usage FILTERXML workbook excel

Hi, FILTERXML in combination with array function returns the same value for all cells. In the example attached, I'd expect  "Dlow" and  "Sri Lankan presidential election, 2015" for the first two entries as of today (jan 9 , 2015) , but I get "Dlow" and "Dlow". It would be nice to have this fixed:)

Thanks, Oyvind
Comment 1 m_a_riosv 2015-01-10 02:56:11 UTC
Created attachment 112041 [details]
Sample file

Hi, thanks for reporting.

I'm not sure if the used Xpath expression it's fine to get all records without define what number of record get.

In the attached one sample on how get every record.

I don't know about Xpath, but I'm curious about, obtaining some information in http://www.w3schools.com/xpath/xpath_examples.asp

Not clear for me this is a bug, and if your expression must work in the way you expect. Does it work with Excel?
Comment 2 Oyvind 2015-01-10 06:43:57 UTC
Thanks for responding so quickly. As for your question about excel, the attached sample works in excel. See https://support.office.com/en-us/article/FILTERXML-function-4df72efc-11ec-4951-86f5-c1374812f5b7?ui=en-US&rs=en-001&ad=US

It seems natutal that this should work, as you would like to represent a list of entries from a webservice in a sheet this way. 

Hope this helps,
Oyvind
Comment 3 m_a_riosv 2015-01-10 14:08:31 UTC
Seems that the function only returns the first element.
Comment 4 QA Administrators 2016-01-17 20:04:47 UTC Comment hidden (obsolete)
Comment 5 m_a_riosv 2016-01-18 09:25:49 UTC
Seems it is solved for:
Win10x64
Version: 5.1.0.2 (x64)
Build ID: ecd3574d51754b043f865cf5bafee286d24db7cc
CPU Threads: 4; OS Version: Windows 6.19; UI Render: GL; 

but remains with:
Version: 5.0.4.2 (x64)
Build ID: 2b9802c1994aa0b7dc6079e128979269cf95bc78
Comment 6 m_a_riosv 2016-01-26 00:53:47 UTC
Seems solved for:
Version: 5.0.5.1 (x64)
Build ID: 7609023f63524a6c8326f6c82e7e23f55a5b7bb5
Comment 7 aborruso 2016-03-11 15:45:57 UTC
Not solved for 5.1.1.3 (x64) version for windows
Comment 8 m_a_riosv 2016-03-28 01:42:48 UTC
Hi @aborruso, are you sure you have set up the formula properly?.

The first file in this report doesn't work for me, but changing the formula to:
A5: =FILTERXML(A$3;"//rc["&(ROW()-4)&"]/@title")
it does.

and my second sample file works for me.
Comment 9 m_a_riosv 2016-04-06 22:32:00 UTC
Sorry for the mistake.
Reviewing issue, it's clear that the function doesn't work properly. It doesn't return an array where there are several values.

On the new sample attached
{=FILTERXML($A$3;"//book/title")}

Workaround it's call every row of data explicitly. 
{=FILTERXML($A$3;"//book["&(ROW()-6)&"]/title")}
Comment 10 m_a_riosv 2016-04-06 22:32:27 UTC
Created attachment 124138 [details]
New sample
Comment 11 mahfiaz 2016-04-19 06:42:14 UTC
Created attachment 124487 [details]
A new example

I can confirm this. In Excel if used in array, the first element in the FILTERXML query gets numerated (will the second one be also numerated for 2D arrays?).

I simplified the example slightly. I suppose it replaces all previous ones.
Comment 12 mahfiaz 2016-04-19 06:45:02 UTC
I mark this as new/enhancement request.
Comment 13 Commit Notification 2016-08-17 14:03:32 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7d733e16513a1b9413303d60feb03c5a0de941fc

Resolves: tdf#88257 handle FILTERXML array/matrix context

It will be available in 5.3.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 14 mahfiaz 2016-08-17 14:30:30 UTC
Yay!
Comment 15 m_a_riosv 2016-08-18 23:20:59 UTC
Thanks Eike, nice.

Verified
Win10x64
Version: 5.3.0.0.alpha0+
Build ID: 327f5cc66f122979943d2c896eb1824791a854c9
CPU Threads: 4; OS Version: Windows 6.19; UI Render: GL; 
TinderBox: Win-x86@42, Branch:master, Time: 2016-08-17_23:39:34
Locale: es-ES (es_ES); Calc: CL
Version: 5.3.0.0.alpha0+ (x64)
Build ID: 327f5cc66f122979943d2c896eb1824791a854c9
CPU Threads: 4; OS Version: Windows 6.19; UI Render: GL; 
TinderBox: Win-x86_64@62-TDF, Branch:MASTER, Time: 2016-08-18_00:02:16
Locale: es-ES (es_ES); Calc: CL

Perhaps it could be backported.
Comment 16 Eike Rathke 2016-08-29 13:13:40 UTC
Pending review https://gerrit.libreoffice.org/28460 for 5-2
Comment 17 Commit Notification 2016-08-30 14:15:59 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7f6770fac8787203220cd73246ffc058a1b14d00&h=libreoffice-5-2

Resolves: tdf#88257 handle FILTERXML array/matrix context

It will be available in 5.2.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.