Howto extract Domain name from an ENTSOE market document

[Wilop91]

Hello
I wrote several scripts to retrieve data from the ENTSOE website.
Now I'm a little bit more familiar with LUA and DZVENTS, I'm trying to improve my scripts.

Below is an example of an XML document with the Border Flows (in MW) between France and Italie
From this document I would lile to extract the in_Domain and out_Domain fields.
For this I use this syntax

Code: Select all

InDomain = item.xml.Publication_MarketDocument.TimeSeries.in_Domain.mRID
OutDomain = item.xml.Publication_MarketDocument.TimeSeries.out_Domain.mRID

But this returns a nil value.
Just for info in the XML document those fields are like this:

Code: Select all

<in_Domain.mRID codingScheme="A01">10YIT-GRTN-----B</in_Domain.mRID>
<out_Domain.mRID codingScheme="A01">10YFR-RTE------C</out_Domain.mRID>

What is particular with this field is it contains a "codingScheme"
What I would like is the contents, i.e. 10YIT-GRTN-----B and 10YFR-RTE------C which are the offical ENTSOE identifiers.
Someone know hot to achieve this?

Thanks in advance

Willy

Code: Select all

<Publication_MarketDocument xmlns="urn:iec62325.351:tc57wg16:451-3:publicationdocument:7:0">
<mRID>8a97af39f7dd432685d2ca3d92d006c3</mRID>
<revisionNumber>1</revisionNumber>
<type>A11</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>2024-07-21T09:28:02Z</createdDateTime>
<period.timeInterval>
<start>2024-07-19T23:00Z</start>
<end>2024-07-20T23:00Z</end>
</period.timeInterval>
<TimeSeries>
<mRID>1</mRID>
<businessType>A66</businessType>
<in_Domain.mRID codingScheme="A01">10YIT-GRTN-----B</in_Domain.mRID>
<out_Domain.mRID codingScheme="A01">10YFR-RTE------C</out_Domain.mRID>
<quantity_Measure_Unit.name>MAW</quantity_Measure_Unit.name>
<curveType>A01</curveType>
<Period>
<timeInterval>
<start>2024-07-19T23:00Z</start>
<end>2024-07-20T23:00Z</end>
</timeInterval>
<resolution>PT60M</resolution>
<Point>
<position>1</position>
<quantity>2943</quantity>
</Point>
<Point>
<position>2</position>
<quantity>2996</quantity>
</Point>
<Point>
<position>3</position>
<quantity>2848</quantity>
</Point>
<Point>
<position>4</position>
<quantity>2848</quantity>
</Point>
<Point>
<position>5</position>
<quantity>2804</quantity>
</Point>
<Point>
<position>6</position>
<quantity>2824</quantity>
</Point>
<Point>
<position>7</position>
<quantity>2943</quantity>
</Point>
<Point>
<position>8</position>
<quantity>2830</quantity>
</Point>
<Point>
<position>9</position>
<quantity>2652</quantity>
</Point>
<Point>
<position>10</position>
<quantity>2831</quantity>
</Point>
<Point>
<position>11</position>
<quantity>2785</quantity>
</Point>
<Point>
<position>12</position>
<quantity>2562</quantity>
</Point>
<Point>
<position>13</position>
<quantity>2164</quantity>
</Point>
<Point>
<position>14</position>
<quantity>2034</quantity>
</Point>
<Point>
<position>15</position>
<quantity>2762</quantity>
</Point>
<Point>
<position>16</position>
<quantity>3141</quantity>
</Point>
<Point>
<position>17</position>
<quantity>3174</quantity>
</Point>
<Point>
<position>18</position>
<quantity>2702</quantity>
</Point>
<Point>
<position>19</position>
<quantity>2421</quantity>
</Point>
<Point>
<position>20</position>
<quantity>2238</quantity>
</Point>
<Point>
<position>21</position>
<quantity>2657</quantity>
</Point>
<Point>
<position>22</position>
<quantity>2552</quantity>
</Point>
<Point>
<position>23</position>
<quantity>2696</quantity>
</Point>
<Point>
<position>24</position>
<quantity>3135</quantity>
</Point>
</Period>
</TimeSeries>
</Publication_MarketDocument>

[janpep]

I am not 100% sure. (not tested)
Just a quick guess that the 'codingScheme' is put as the first and your 'mRID' is put as second in the table?

Then

Code: Select all

item.xml.Publication_MarketDocument.TimeSeries.in_Domain.mRID[1].codingScheme

might give you the "A01" and the following would give your mRID's

Code: Select all

InDomain  = item.xml.Publication_MarketDocument.TimeSeries.in_Domain.mRID[2]
OutDomain = item.xml.Publication_MarketDocument.TimeSeries.out_Domain.mRID[2]

[Wilop91]

Hello janpep
Thanks a lot for your reply.
Unfortunately this did not work (I tried all sorts of combinations)
When I use "pairs" to list all fields under Timeseries I get this

Code: Select all

2024-07-21 14:30:01.923 dzVents: RunAllScripts: My responseKey = mRID
2024-07-21 14:30:01.922 dzVents: RunAllScripts: My responseKey = businessType
2024-07-21 14:30:01.922 dzVents: RunAllScripts: My responseKey = in_Domain.mRID
2024-07-21 14:30:01.922 dzVents: RunAllScripts: My responseKey = out_Domain.mRID
2024-07-21 14:30:01.923 dzVents: RunAllScripts: My responseKey = quantity_Measure_Unit.name
2024-07-21 14:30:01.922 dzVents: RunAllScripts: My responseKey = curveType

But when I try to read in_Domain.mRID I get nil.
I guess this is related with the codingScheme. I need to search on the ENTSOE web sote for examples explais how to handle this

Willy

[janpep]

Maybe try a dump of the table to find out how this is stored.

[Kedi]

Would not this kind of coding get you a table or a value. (Just an example)

Code: Select all

local result_table = dz.utils.fromXML(item.data)['Publication_MarketDocument']['TimeSeries']['Period']['Point']

[waltervl]

This "sender_MarketParticipant.mRID codingScheme="A01"" seems a little bit tricky due to is name with a ., space and " in it.
So probably use something as Kedi aready proposed

Code: Select all

local MarketParticipant = dz.utils.fromXML(item.data)['sender_MarketParticipant.mRID codingScheme="A01"']

[Wilop91]

Hello
First of all thanks for your replies.
I need to clarify a bit more to be sure we have the same understanding
I lauch an URL with this syntax

Code: Select all

dz.openURL({url = EUurl, method = 'GET', callback = 'EUborderFlows'}

The URL is something I can view in my browser, and this is what I enclosed in my first post (below I removed my API security Token)

Code: Select all

https://web-api.tp.entsoe.eu/api?securityToken=xxxxxxxx&documentType=A11&in_Domain=10YIT-GRTN-----B&out_Domain=10YFR-RTE------C&periodStart=202407192300&periodEnd=202407202300

I get an answer HTTPResponse which I process like this

Code: Select all

if (item.isHTTPResponse ) then
    if debug then dz.log("HTTP Response on: "..item.trigger, dz.LOG_INFO) end
    MyTrigger = item.trigger        
    if MyTrigger=="EUborderFlows"  and item.ok then
        if item.xml.Acknowledgement_MarketDocument then
            dz.log('GenPerType - Error in XML - '.. item.xml.Acknowledgement_MarketDocument.Reason.text,dz.LOG_INFO) 
        else
            ProcessBorderFlows ()
        end
    end  -- if item.trigger=="EUborderFlows" and item.ok then
end

So I assume the HTTPResponse has been converted into a LUA table. Correct?

From this table I can extract and use all fields except the in_Domain and out_Domain fields because the contents of these 2 fields contains a "codingScheme" which I suupose should tell how to interpret its contents.

Code: Select all

<in_Domain.mRID codingScheme="A01">10YIT-GRTN-----B</in_Domain.mRID>
<out_Domain.mRID codingScheme="A01">10YFR-RTE------C</out_Domain.mRID>

The above contents is what I see in my browser (thats why there are the <> tags) but I assume the LUA table do not have these anymore.

I would like to extract "10YIT-GRTN-----B" and "10YFR-RTE------C" which are the official ENTSOE country codes.

Any idea howto to do this?

PS: to be clear I want to determine if the Timeseries I received contains "import' or "export" data (an HTTPResponse can contain several Timeseries)

Willy

[janpep]

So I assume the HTTPResponse has been converted into a LUA table. Correct?

Yes. You can also test this by check
if item.isXML then ...
WIKI says: "This is true when the customEvent data is a valid xml string. When true, the data is automatically converted to a Lua table."

And then the test:
if type( item.xml.Publication_MarketDocument ) == "table" then ...
can confirm that you have a table as result.

The problematic fields in the XML are.

Code: Select all

<in_Domain.mRID codingScheme="A01">10YIT-GRTN-----B</in_Domain.mRID>
<out_Domain.mRID codingScheme="A01">10YFR-RTE------C</out_Domain.mRID>

I think it can be caused by the dot in the element name "in_Domain.mRID" or because of the use of the attribute "codingScheme" as waltervl pointed out.
That makes it harder to guess how this is converted into the table.
Thats why I suggested to dump the table. Then you see the structure and content.

Code: Select all

domoticz.utils.dumpTable(table,[levelIndicator],[osfile]3.0.0)) 

Function: print table structure and contents to log. See wiki.

[Wilop91]

Hello
The document I receive is in "table" format.
I dumped the table and extracted the 2 fields (otherwise the log is too long)

Code: Select all

2024-07-22 21:09:01.635 dzVents: in_Domain.mRID:
2024-07-22 21:09:01.635 dzVents: 1: 10YFR-RTE------C
2024-07-22 21:09:01.635 dzVents: _attr:
2024-07-22 21:09:01.635 dzVents: codingScheme: A01

2024-07-22 21:09:01.635 dzVents: out_Domain.mRID:
2024-07-22 21:09:01.635 dzVents: 1: 10Y1001A1001A83F
2024-07-22 21:09:01.635 dzVents: _attr:
2024-07-22 21:09:01.635 dzVents: codingScheme: A01

I tried several syntaxes but could not get it to work.
Anyone xan help me?
Thanks
Willy

[janpep]

Code: Select all

2024-07-22 21:09:01.635 dzVents: in_Domain.mRID:
2024-07-22 21:09:01.635 dzVents: 1: 10YFR-RTE------C
2024-07-22 21:09:01.635 dzVents: _attr:
2024-07-22 21:09:01.635 dzVents: codingScheme: A01

I tried several syntaxes but could not get it to work.
Anyone xan help me?

And where is the 1: in the second line coming from?
Can it be rownumber colon value?
What does the following give you?

Code: Select all

item.xml.Publication_MarketDocument.TimeSeries.in_Domain.mRID[1]

[Wilop91]

Hello
Thanks for your reply
Your suggestion gives the following error:

Code: Select all

2024-07-22 22:26:01.633 Error: dzVents: RunAllScripts: An error occurred when calling event handler RunAllScripts
2024-07-22 22:26:01.633 Error: dzVents: RunAllScripts: ...ticz/scripts/dzVents/generated_scripts/RunAllScripts.lua:428: attempt to index a nil value (field 'in_Domain')

I have to admit I'm confused with this particular field ....
Willy

[janpep]

Yes indeed
It looks like the error shows that it sees 'in_Domain' as a field and not the entire 'in_Domain.mRID'.
So it might work when it recognizes the entire fieldname.
I do not know if there is a way to escape the dot.
When that is not possible parsing the XML content yourself would be the latest option I can think off.
Jan

[Kedi]

Would not this kind of coding get you a table or a value. (Just an example)

Code: Select all

local result_table = dz.utils.fromXML(item.data)['Publication_MarketDocument']['TimeSeries']['Period']['Point']

So did you try this way of coding to bypass the dot?

[Wilop91]

Hello Kedi and Janpep
I got it working!
These 2 particular fields are "tables" and need to be logged as tables while I was logging it as a simple variable which was giving NIL
This works:

Code: Select all

local inDomain = dz.utils.fromXML(item.data)['Publication_MarketDocument']['TimeSeries']['in_Domain.mRID']
for i=1,#inDomain do dz.log('EUborderFlows - Index = ' .. i .. ' inDomain = ' .. inDomain[i] ,dz.LOG_INFO) end

Which gives

Code: Select all

2024-07-23 09:38:01.891 dzVents: RunAllScripts: HTTP Response on: EUborderFlows
2024-07-23 09:38:01.925 dzVents: RunAllScripts: EUborderFlows - Index = 1 inDomain = 10YFR-RTE------C

PS: the following syntax gives the error "attempt to index a nil value (field 'in_Domain')". Thats why it has to be "escaped" as suggested by Kedi

Code: Select all

local inDomain = item.xml.Publication_MarketDocument.TimeSeries.in_Domain.mRID

Thanks a lot for your help

Willy

[janpep]

Good. Congratulations.
So you can also call it directly with:

Code: Select all

dz.utils.fromXML(item.data)['Publication_MarketDocument']['TimeSeries']['in_Domain.mRID'][1]

?

[Wilop91]

Hello Janpep
Yes that syntax also works!
Thanks a lot.
Willy