I have an XML output which looks something like below:

<ROOT>
    <THRESHOLD ADDRESS="1234 Address Rd">
        <DETAIL TERM="A" PERIOD="1" TOTAL="7">
            <ADD_DETAIL CODE="ABC">
            <ADD_DETAIL CODE="ABC">
            <ADD_DETAIL CODE="ABC">
            <ADD_DETAIL CODE="ABC">
            <ADD_DETAIL CODE="ABC">
            <ADD_DETAIL CODE="ABC">
            <ADD_DETAIL CODE="ABC">
        </DETAIL>
        <DETAIL TERM="A" PERIOD="2" TOTAL="6">
            <ADD_DETAIL CODE="DEF">
            <ADD_DETAIL CODE="DEF">
            <ADD_DETAIL CODE="DEF">
            <ADD_DETAIL CODE="DEF">
            <ADD_DETAIL CODE="DEF">
            <ADD_DETAIL CODE="DEF">
        </DETAIL>
        <DETAIL TERM="B" PERIOD="1" TOTAL="7">
            <ADD_DETAIL CODE="ABC">
            <ADD_DETAIL CODE="ABC">
            <ADD_DETAIL CODE="ABC">
            <ADD_DETAIL CODE="ABC">
            <ADD_DETAIL CODE="ABC">
            <ADD_DETAIL CODE="ABC">
            <ADD_DETAIL CODE="ABC">
        </DETAIL>
        <DETAIL TERM="B" PERIOD="2" TOTAL="6">
            <ADD_DETAIL CODE="DEF">
            <ADD_DETAIL CODE="DEF">
            <ADD_DETAIL CODE="DEF">
            <ADD_DETAIL CODE="DEF">
            <ADD_DETAIL CODE="DEF">
            <ADD_DETAIL CODE="DEF">
        </DETAIL>
    </THRESHOLD>
</ROOT>

In my merge document, I already have a {MERGEFIELD TableStart:THRESHOLD} because I also use certain fields from that node, such as {MERGEFIELD @ADDRESS}.

I need to now create a table where each cell is a sum of the TOTAL value from each DETAIL node, matching the corresponding PERIOD in the table.
So, in the above example, I’d ideally have 2 columns outputted. The first column is Period 1, and it should show 14. The second column is Period 2, and it should show 12.

I’d like to manually map out each column for each period, and have it show a SUM of all DETAIL/@TOTAL properties where the DETAIL/@PERIOD is equal to that respective period.
In the example XML above, that would mean 14 for Period 1, and 12 for Period 2.

I’ve been trying every variation of the SUM function but I can’t seem to get it to work.
Please see attempts below and the results I received:

{ =SUM( { MERGEFIELD DETAIL[@PERIOD=’1′]/@TOTAL} ) }
^Above only resulted the first total, 7.

{ =SUM( /DETAIL[@PERIOD=’1′]/@TOTAL ) }
^Above resulted “!Syntax Error”

{ =SUM( { { MERGEFIELD TableStart:DETAIL[@PERIOD=’1′] } { MERGEFIELD @TOTAL } { MERGEFIELD @TableEnd:DETAIL[@PERIOD=’1′] } } ) }
^Above resulted “!Unexpected End of Formula” (a lot of attempts I’ve tried end with this one, I’m assuming it’s because it’s only passing a single mergefield, while SUM would normally require multiple values.

I can keep listing all of the different variations I’ve tried, as I’ve tried probably 12+ different variations now, but they all seem to be giving the same few problems above.
Does anyone know if there’s a way to do this with XPath 2.0 notation?
If so, would you be able to please share the syntax so I could use this?

I can also use something like the 3 mergefields below which will show a list of all @TOTAL values in the one cell, but it doesn’t look very neat, and a total would be much better.
{MERGEFIELD TableStart:DETAIL[@PERIOD=’1′]} {MERGEFIELD @TOTAL} {MERGEFIELD TabelEnd:DETAIL[@PERIOD=’1′]}