0

I'm playing with Excel and the XML API from boardgamegeek, here is a sample page https://www.boardgamegeek.com/xmlapi2/thing?id=230802&stats=1

I already know how to use the functions webservice and filterxml to access to the tags and data, for example I can use //item/name/@value for the name or //item/poll[@name="language_dependence"]/results/result[ 1 ]/@numvotes for the first item on the language dependency poll (without spaces into the brackets)

However I couldn't do the following

  1. I want to apply the max function to find which poll item has the max number of votes, in this example should be "No necessary in-game text", when I try Excel throws me a value error
  2. The numeric values with decimals like //item/statistics/ratings/averageweight/@value are being showed like integers in Excel like in the image

average weight being showed as integer

Thanks for your help

David Owen
  • 159
  • 1
  • 10
  • So which XPath expression exactly do you use for which you get which error exactly? – Martin Honnen Dec 30 '19 at 13:17
  • 1
    In XPath 1.0/2.0 context, number formatting is part of XSLT extensions, thus you must use string manipulation. Starting with XPath 2.0, there is a [max()](https://www.w3.org/TR/xpath-functions/#func-max) function. In XPath 1.0 there is a [common max/min idiom](https://stackoverflow.com/questions/15117692/selecting-the-min-max-value-of-an-attribute-under-multiple-nodes-in-xpath-1-0) – Alejandro Dec 30 '19 at 13:45
  • @Alejandro Excel's filterxml function is limited to XPATH 1.0? I didn't found any documentation about that – David Owen Dec 30 '19 at 13:57
  • @Alejandro In the referenced XML, it seems the numeric values are returned as text strings. That being the case, the comparisons will compare text strings, so, for example `44` will seem to be greater than `4000`. Is there a work-around for that. – Ron Rosenfeld Dec 30 '19 at 19:23
  • @RonRosenfeld Comparison operators `>` and `<` work only as number operators in XPath 1.0 (e.g. [here](http://www.xpathtester.com/xpath/420fb01644ae89979be33f2656abf0ef) – Alejandro Dec 30 '19 at 21:31
  • @Alejandro If I replace one of the `numvotes="0"` with `numvotes = "4040"` in that xml, the `numvotes="44"` is still returned as maximum using `...[not(./@numvotes<../*/@numvotes)]...`If I could use `...[not(./number(@numvotes)<../*/number(@numvotes))]...`, then 4040 would be returned, but that won't work in the Excel xPath argument. – Ron Rosenfeld Dec 30 '19 at 22:42
  • The fixed xpath should be //item/poll[@name='language_dependence']/results/result[not(@numvotes < ../result/@numvotes)]/@value without [1], with that formula returns the language dependence with the max number of votes – David Owen Dec 30 '19 at 23:06
  • @RonRosenfeld My XPath expression works as the link shows. – Alejandro Dec 30 '19 at 23:08
  • 1
    @DavidOwen The `[1]` predicate does not change the result. It's there because it is posible to have more than one node with the maximum value. – Alejandro Dec 30 '19 at 23:13
  • @Alejandro In fact, with that formula worked and worked good, any ideas about the number format? – David Owen Dec 31 '19 at 00:25
  • @DavidOwen I believe that if the decimal setting for your **Windows Regional Settings** is not a **dot**, then you may receive the result you are seeing. – Ron Rosenfeld Dec 31 '19 at 02:20

0 Answers0