insert node at same level as max value of another node-CodePudding

I have an oracle clob holding xml as such

<acctInfo>
    <acctCalculations>
        <acctCalculation>
            <acctCalculationDate>2020-10-20</acctCalculationDate>
            <HouseholdIncome>1960.00</HouseholdIncome>
            <NumberOfOccupants>4</NumberOfOccupants>
            <acctLevel>1</acctLevel>
            <incomeVerificationDate>2020-10-20</incomeVerificationDate>
            <incomeVerifiedBy>CJOE</incomeVerifiedBy>
        </acctCalculation>
        <acctCalculation>
            <acctCalculationDate>2021-01-15</acctCalculationDate>
            <HouseholdIncome>1960.00</HouseholdIncome>
            <NumberOfOccupants>4</NumberOfOccupants>
            <acctLevel>1</acctLevel>
            <incomeVerificationDate>2020-10-20</incomeVerificationDate>
            <incomeVerifiedBy>CJOE</incomeVerifiedBy>
       </acctCalculation>
        <acctCalculation>
            <acctCalculationDate>2021-10-01</acctCalculationDate>
            <HouseholdIncome>0</HouseholdIncome>
            <NumberOfOccupants>4</NumberOfOccupants>
            <acctLevel>1</acctLevel>
            <incomeVerificationDate>2021-10-01</incomeVerificationDate>
            <incomeVerifiedBy>BYAJRIS</incomeVerifiedBy>
        </acctCalculation>
    </acctCalculations>
</acctInfo>

I need to add a node like <NetHouseholdIncome>15</NetHouseholdIncome> after <acctCalculationDate>2021-10-01</acctCalculationDate> - since it is the max calculation date

So it would look like

<acctCalculation>
    <acctCalculationDate>2021-10-01</acctCalculationDate>

* <NetHouseholdIncome>15</NetHouseholdIncome>*

    <HouseholdIncome>0</HouseholdIncome>
    <NumberOfOccupants>4</NumberOfOccupants>
    <acctLevel>1</acctLevel>
    <incomeVerificationDate>2021-10-01</incomeVerificationDate>
    <incomeVerifiedBy>BYAJRIS</incomeVerifiedBy>
</acctCalculation>

so i think something similar to bellow should work but i can't seem to find what the xpath should be to get the max value of a node at the same level i want to insert.

select
   to_clob(
       APPENDCHILDXML(
          xmltype(field_Data_area),
          '*/acctInfo/acctCalculations/acctCalculation[3]*',
          xmltype('<NetHouseholdIncome>15</NetHouseholdIncome>')
       )
   )

i also thought about passing the original xml into an xmltable using "position for ordinality" to get the node of the max value and updating at that node but I wasn't sure if it would maintain the same node count when i used the position number in the update xml statement

select 
    AcctCalculationDate, position
from 
    data_area
    XMLTABLE ('/acctInfo/acctCalculations/acctCalculation' --id row in table
    passing xmltype('FIELD_DATA_AREA')
    columns
    position for ordinality,
    acctCalculationDate varchar2(12) path 'acctCalculationDate'
) xml

I tried finding the xmlpath query to get the max value node. And thought about using two queries one to get the number of the node and then a query updating via that node but I wasn't sure if the node number would remain consistent. looking for the syntax to get the max node or... just strategy I should use to insert these values

CodePudding user response：

with q as
 (
   select 
    max(t.position) keep(dense_rank last order by acctCalculationDate) pos
 from data_area, 
  xmltable('/acctInfo/acctCalculations/acctCalculation' 
             passing field_Data_area 
             columns 
              acctCalculationDate date path 'acctCalculationDate',
              position for ordinality
          ) t
 )
    select 
appendChildXML(data_area.field_Data_area,
                       '/acctInfo/acctCalculations/acctCalculation['||
                         q.pos||
                         ']',xmltype('<NetHouseholdIncome>15</NetHouseholdIncome>'))
                         
from data_area, 
  q

works for me, if you have just one clob in your data_area table.