SQL & PL/SQL

Hello,

I can write SQL/ - PL/SQL "straightforward" but now I have a problem what is to big for me.  I hop that someone can help.

We create by code a xml structure and write these into the oracle database CLOB field. I'm sorry to say that a xml text structure is written into a clob in stead of a xmltype field. (it's a design failure?) It's a large xml structure. I believe I can't attach a file so I put at the end of this discussion a light example of the xml structure.

It's a xml with quartervalue's, so there are 35040 detail rows (24h * 4 * 365days). I want to accumulate the the attribute Amount value 9. The amount value is in the Detail node a attribute but at the end you can see that for the DST are also 4 value's, but these are not attribute value's but node value's. (In this case it are four value's in some cases there is one DST amount value.

Can somebody help me how to accumulate all the detail attribute value Amount with the node value Amount of the DST tag?

XML structure:

<?xml version="1.0"?>

<Message xmlns:ns1="http://automaticdealcapture/">

    <BusinessDocument messageDateTime="2013-10-25T13:59:31+02:00" ediReference="LO-461967" messageName="New" businessSector="Z" documentFunction="Original">

        <DocumentData>

            <ns1:Adcs>

                <ns1:Package>

                    <ns1:Deal>

                        <ns1:ProductCode>PWCODE</ns1:ProductCode>

                        <ns1:Action>NEW</ns1:Action>

                        <ns1:Memo1>MemoField</ns1:Memo1>

                        <ns1:Details>

                            <ns1:Detail Dates="2014-01-01" Datee="2014-01-01" Times="00:00:00" Timee="01:00:00" Amount="0.0153" Price="11.111"/>

                            <ns1:Detail Dates="2014-01-01" Datee="2014-01-01" Times="01:00:00" Timee="02:00:00" Amount="0.015" Price="22.222"/>

                            etc. 350040 detail rows.

                        </ns1:Details>

                        <ns1:DSTS>

                            <ns1:Year Val="2014">

                                <ns1:DST Period="1">

                                    <ns1:Amount>0.0146</ns1:Amount>

                                    <ns1:Price>33.333</ns1:Price>

                                </ns1:DST>

                                <ns1:DST Period="2">

                                    <ns1:Amount>0.0222</ns1:Amount>

                                    <ns1:Price>33.333</ns1:Price>

                                </ns1:DST>

                                <ns1:DST Period="3">

                                    <ns1:Amount>0.0444</ns1:Amount>

                                    <ns1:Price>33.333</ns1:Price>

                                </ns1:DST>

                                <ns1:DST Period="4">

                                    <ns1:Amount>0.0146</ns1:Amount>

                                    <ns1:Price>33.333</ns1:Price>

                                </ns1:DST>

                            </ns1:Year>

                        </ns1:DSTS>

                    </ns1:Deal>

                </ns1:Package>

            </ns1:Adcs>

        </DocumentData>

    </BusinessDocument>

</Message>