Help with Xpath expression or transformation of it

Add-ons Other
1

Hi, This is my first post so forgive me if I do not do this right.

Im trying to parce data retreived from a HTTP Get command to get the dayahead power prices.

The HTTP GET works fine and I retreive a Xml response similar to this:


<Publication_MarketDocument xmlns="urn:iec62325.351:tc57wg16:451-3:publicationdocument:7:0">
<mRID>2a46ebc6a7f64648b07161450e647378</mRID>
<revisionNumber>1</revisionNumber>
<type>A44</type>
<sender_MarketParticipant.mRID codingScheme="A01">10X1001A1001A450</sender_MarketParticipant.mRID>
<sender_MarketParticipant.marketRole.type>A32</sender_MarketParticipant.marketRole.type>
<receiver_MarketParticipant.mRID codingScheme="A01">10X1001A1001A450</receiver_MarketParticipant.mRID>
<receiver_MarketParticipant.marketRole.type>A33</receiver_MarketParticipant.marketRole.type>
<createdDateTime>2022-08-07T21:07:44Z</createdDateTime>
<period.timeInterval>
<start>2022-08-07T22:00Z</start>
<end>2022-08-08T22:00Z</end>
</period.timeInterval>
<TimeSeries>
<mRID>1</mRID>
<businessType>A62</businessType>
<in_Domain.mRID codingScheme="A01">10YNO-2--------T</in_Domain.mRID>
<out_Domain.mRID codingScheme="A01">10YNO-2--------T</out_Domain.mRID>
<currency_Unit.name>EUR</currency_Unit.name>
<price_Measure_Unit.name>MWH</price_Measure_Unit.name>
<curveType>A01</curveType>
<Period>
<timeInterval>
<start>2022-08-07T22:00Z</start>
<end>2022-08-08T22:00Z</end>
</timeInterval>
<resolution>PT60M</resolution>
<Point>
<position>1</position>
<price.amount>301.09</price.amount>
</Point>
<Point>
<position>2</position>
<price.amount>263.70</price.amount>
</Point>
<Point>
<position>3</position>
<price.amount>273.33</price.amount>
</Point>
<Point>
<position>4</position>
<price.amount>270.32</price.amount>
</Point>
<Point>
<position>5</position>
<price.amount>264.97</price.amount>
</Point>
<Point>
<position>6</position>
<price.amount>334.91</price.amount>
</Point>
<Point>
<position>7</position>
<price.amount>361.72</price.amount>
</Point>
<Point>
<position>8</position>
<price.amount>406.97</price.amount>
</Point>
<Point>
<position>9</position>
<price.amount>401.83</price.amount>
</Point>
<Point>
<position>10</position>
<price.amount>372.63</price.amount>
</Point>
<Point>
<position>11</position>
<price.amount>329.60</price.amount>
</Point>
<Point>
<position>12</position>
<price.amount>297.55</price.amount>
</Point>
<Point>
<position>13</position>
<price.amount>284.79</price.amount>
</Point>
<Point>
<position>14</position>
<price.amount>285.48</price.amount>
</Point>
<Point>
<position>15</position>
<price.amount>286.13</price.amount>
</Point>
<Point>
<position>16</position>
<price.amount>298.85</price.amount>
</Point>
<Point>
<position>17</position>
<price.amount>320.29</price.amount>
</Point>
<Point>
<position>18</position>
<price.amount>397.73</price.amount>
</Point>
<Point>
<position>19</position>
<price.amount>422.04</price.amount>
</Point>
<Point>
<position>20</position>
<price.amount>435.77</price.amount>
</Point>
<Point>
<position>21</position>
<price.amount>433.46</price.amount>
</Point>
<Point>
<position>22</position>
<price.amount>424.95</price.amount>
</Point>
<Point>
<position>23</position>
<price.amount>415.84</price.amount>
</Point>
<Point>
<position>24</position>
<price.amount>379.07</price.amount>
</Point>
</Period>
</TimeSeries>
</Publication_MarketDocument>

I need help to create a XPath expression to retreive any of the Price.Amount values. currently I have read tons of posts and have ended up trying two promissing methods.

Also, Xpath transformation is installed.

method1:

I have created a HTTP thing with a channel.

Thing http:url:powerPrices "Power Prices" [
	baseURL="MYUrl",	
	refresh=3600] {
		Channels:
			Type string : text "Text" [ stateTransformation="XPath:/Publication_MarketDocument/TimeSeries/Period/Point[1]/price.amount/text()" ]
   }

And a Item that correspond to this.

String PowerPrice1      "Pris Kl 1"                {channel="http:url:powerPrices:text"}

the Items returns empty.
Additionally, the URL will need to change every day as there is a date attribute in the URL, so to my limited knowledge I do not see how I can use this method anyway to retreive the values?

Method 2:

I have created a Power.rules file:

rule "Test"
when
Item PowerTestSwitch received command
then
val String priceXml = sendHttpGetRequest("MYUrl")
   logInfo("Mytest1", priceXml)
val String transxpath = transform("XPATH", "/Publication_MarketDocument/TimeSeries/Period/Point[1]/price.amount/text()", priceXml)

  logInfo("Mytest2", transxpath)

loginfo Mytest1 returns the full Xml as expected.
the following is the error for logInfo Mytest2:

00:28:02.443 [ERROR] [.internal.handler.ScriptActionHandler] - Script execution of rule with UID 'power-1' failed: Text '<?xml version="1.0" encoding="UTF-8"?>
<Publication_MarketDocume...' could not be parsed at index 0 in power

Additionally, when loading the full xml into various online XPath expression testers and expression builders, some of them verifies the expression, and others returns nothing on the same expression.

Can someone point me in the right direction?

2

Hi,

Regarding the URL including dynamic date parts, please look into the documentation of http binding, as you can include dynamic date parts into the url of the http thing (is the url formatting section at the end of the page)

Regarding the issue that xpath is not working I have no real advice. Maybe just try with other more generic expressions (e.g. /Publication_MarketDocument/TimeSeries only), to verify if your xpath in general is working fine.

Just as a guess, but maybe the . within the path is an issue and needs to be escaped somehow?

I also saw cases where the xml was not well formatted and had issues, therefore xpath was not working at all and you have to use regex instead

3

Thanks for the hint about the dynamic URL. Will look into this.

Regarding the xml, I do suspect that the formatting could be bad as nothing I try returns something meaningful.

I Went ahead and tried with regex instead.

val String transregex = transform("REGEX", ".*<price\\.amount>([0-9]*\\.[0-9]*).*", priceXml)

returns the price for the first entry of the <price.amount>. I need to retreive one for each of the 24 entries. Any ideas?

The code

val String transregex = transform("REGEX", ".*<position>1<\\/position>\\n<price\\.amount>([0-9]*\\.[0-9]*).*", priceXml)

was an attempt to regex with 2 lines as the line above has a number. But it does not work.

I really appriciate any help!

Thank you

4

Try if this works:

val String transregex = transform("REGEX", "s/.*<price\.amount>([0-9,\.]+)<\/price.amount>.*/$1/g", priceXml)
5

Sorry Wolfgang_S, no luck. The provided regex returns the full xlm. I tried to modify it with help of regex101 but still no luck.

Meanwhile, I have tried to transform the xlm to Json with xml2json.xsl.

This I have done with luck and are now able to extract the data with Json.

But is it then possible to use create a HTTP binding channel that can get the data through this route?

If not, I will just use rules to create my items.

Thank you

6

Hi @Cato_Fuglestad I have exactly the same situation, and I came up until the point I did het xml translated into json, but I can’t parse the daily values out of it. And I also do expect a problem with the dot in de the field ‘price.amount’ .
Could share your JSONPATH statement for the hourly values??

many thanks!

7

Sure. Here is my working rule code. Sorry it not pretty I know. I ended up using Regex instead.
Also, Since price could be anything from 1digit,2decimals (like 1,23) all the way up to 4digit,2decimals (like 1234,56) And I dont know how to write a smarter code, it became cases to try.

val String priceXml = sendHttpGetRequest(“https://transparency.entsoe.eu/api?securityToken=” + token + “&documentType=A44&in_Domain=” + area + “&out_Domain=” + area + “&periodStart=” + timestart + “&periodEnd=” + timeend);

val String transJson = transform(“XSLT”, “xml2json.xsl”, priceXml)
logInfo(“Entso-E API”, “Call returned: {}”, transJson)

//update the DayAheadPrice pr hour
for(var i=1; i<25; i=i+1){
try { //try with 4+2 digits for price
var price= (Float::parseFloat(transform(“REGEX”, “.position":\{"#":"" + i + ""\},"price\.amount":\{"#":"(…\…).”, transJson))) * PowerEURtoNOKore.state as Number
price = price +1 // adds the 1 øre “påslag”
postUpdate(“DayAheadPrice”+i, price.toString)
//logInfo(“4+2 test”,“Hour {} was successful and has price = {}”,i,price.toString)
}
catch(Throwable t) {
//logError(“4+2 test”, "Hour {} failed 4+2: ",i.toString)
}
try { //try with 3+2 digits for price
var price= (Float::parseFloat(transform(“REGEX”, “.position":\{"#":"" + i + ""\},"price\.amount":\{"#":"(…\…).”, transJson))) * PowerEURtoNOKore.state as Number
price = price +1 // adds the 1 øre “påslag”
postUpdate(“DayAheadPrice”+i, price.toString)
//logInfo(“3+2 test”,“Hour {} was successful and has price = {}”,i,price.toString)
}
catch(Throwable t) {
//logError(“3+2 test”, "Hour {} failed 3+2: ",i.toString)
}
try { //try with 2+2 digits for price
var price= (Float::parseFloat(transform(“REGEX”, “.position":\{"#":"" + i + ""\},"price\.amount":\{"#":"(…\…).”, transJson))) * PowerEURtoNOKore.state as Number
price = price +1 // adds the 1 øre “påslag”
postUpdate(“DayAheadPrice”+i, price.toString)
//logInfo(“2+2 test”,“Hour {} was successful and has price = {}”,i,price.toString)
}
catch(Throwable t) {
//logError(“2+2 test”, "Hour {} failed 2+2: ",i.toString)
}
try { //try with 1+2 digits for price
var price= (Float::parseFloat(transform(“REGEX”, “.position":\{"#":"" + i + ""\},"price\.amount":\{"#":"(.\…).”, transJson))) * PowerEURtoNOKore.state as Number
price = price +1 // adds the 1 øre “påslag”
postUpdate(“DayAheadPrice”+i, price.toString)
//logInfo(“1+2 test”,“Hour {} was successful and has price = {}”,i,price.toString)
}
catch(Throwable t) {
//logError(“1+2 test”, "Hour {} failed 1+2: ",i.toString)
}

}

Hope this helps.

8

I’m no XPath expert, but you might try an expression like this as a workaround to the apparently problematic use of “dot” in the element name “price.amount”:

//Point[1]/*[name()="price.amount"]/text()

The trick here is to select all child nodes of Point[1] (“*”) then picking the right one with the condition (“”) that the name() of the element node must equal the quoted string.
I did not try this myself, but it should allow you to use an XPath expression as you originally intended, instead of using a (probably) more complicated and (possibly) less reliable Regex (although I really like Regex for most anything, for xml and json it is usually not the best solution)

9

Many thanks! does help a lot!

I did some thinking of my own and came up with these 2 lines of code in Rules;

val mytest2 = transform("XSLT", "xml2json.xsl", DayAheadenergyprices_TimeSeries.state.toString.replace("price.amount","priceamount"));   
val tarief  = transform("JSONPATH", "$.Publication_MarketDocument.TimeSeries.Period.Point[2].priceamount",mytest2)

the ‘dot’ problem in price.amount is solved and by adding a loop from 1 to 23, I can get the hourly tarifs out of the respons.

10

I’m a bit late to this party but I found it easier to first transform the entsoe XML to JSON and then parse that. See Control a water heater and ground source heat pump based on cheap hours of spot priced electricity - #13 by masipila