Wsrtjtyk

Everything below I am able to do in separate operations, but as I am relatively new to XQuery I am struggling to work out how to perform multiple operations in one go which would be neat.

I am trying to update an XML column, with some xml data from another column (this XML has come from a spreadsheet with two columns, promotion numbers and the department numbers within each promotion). Which I load into a table and then run the below against.

INSERT INTO proms

select promid,  '<Promotion><MultibuyGroup><MMGroupID>'||depts||'</MMGroupID> 

</MultibuyGroup></Promotion>' DEPTS

from (

     SELECT promid, listagg (id,'</MMGroupID><MMGroupID>')  within GROUP 

 (ORDER BY id) as depts FROM mmgroups 

 GROUP BY promid

 );

Creating a table with a column for PROMID and an XML Column like the below (just one MMGroup in example for ease of read.

   <Promotion><MultibuyGroup><MMGroupID>1</MMGroupID></Promotion></MultibuyGroup>

When I run the below, I can successfully update any XML in the PROMOTIONS where the value of ID column matches the value of PROMID in the table I have created above.

merge into PROMOTIONS tgt  

using (  

 select PROMID 

      , xmlquery('/Promotion/MultibuyGroup/MMGroupID'  

          passing xmlparse(document DEPTS)  

          returning content  

        ) as new_mmg  

 from PROMS WHERE PROMID  = 'EMP35Level1'



) src  

 on (tgt.ID = src.PROMID)  



 when matched then update  

  set tgt.xml =  

     xmlserialize(document  

       xmlquery(  

         'copy $d := .  

         modify             

          insert node $new_mmg as last into  $d/Promotion/MultibuyGroup 

          return $d'  

          passing xmlparse(document tgt.xml)  

                 , src.new_mmg as "new_mmg"  

         returning content  

        )  

       no indent  

      )    ;

However what I would like my query to do is to delete any existing MMGroupID nodes from the target xml (if they exist), then replace them with all of the nodes from source xml.

Also within the target xml is a LastUpdated Node which I would like to update with the SYSDATE at time of update

Also two separate columns LAST_UPDATED DATE and ROW_UPDATED NUMBER (20,0) which has the epoch time of update would be nice to update at the same time.

        <Promotion>

        <LastUpdated>2018-08-23T14:56:35+01:00</LastUpdated>

        <MajorVersion>1</MajorVersion>

        <MinorVersion>52</MinorVersion>

        <PromotionID>EMP35Level1</PromotionID>

        <Description enabled="1">Staff Discount 15%</Description>

        <MultibuyGroup>

            <AlertThresholdValue>0.0</AlertThresholdValue>

            <AlertThresholdValue currency="EUR">0.0</AlertThresholdValue>

            <UseFixedValueInBestDeal>0</UseFixedValueInBestDeal>

            <UpperThresholdValue>0.0</UpperThresholdValue>

            <UpperThresholdValue currency="EUR">0.0</UpperThresholdValue>

            <GroupDescription>Employee Discount 15%</GroupDescription>

            <Rolling>0</Rolling>

            <DisableOnItemDiscount>1</DisableOnItemDiscount>

            <UniqueItems>0</UniqueItems>

            <AllItems>0</AllItems>

            <RoundingRule>3</RoundingRule>

            <UseLowestNetValue>0</UseLowestNetValue>

            <TriggerOnLostSales>0</TriggerOnLostSales>

            <MMGroupID>2</MMGroupID>

            <MMGroupID>8</MMGroupID>

            <MMGroupID>994</MMGroupID>

        </MultibuyGroup>

        <Timetable>

            <XMLSchemaVersion>1</XMLSchemaVersion>

            <CriterionID/>

            <StartDate>1970-01-01T00:00:00+00:00</StartDate>

            <FinishDate>2069-12-31T00:00:00+00:00</FinishDate>

        </Timetable>

        <AllowedForEmployeeSale>1</AllowedForEmployeeSale>

        <Notes enabled="1"/>

        <AlertMessage enabled="1"/>

    </Promotion>

Since posting, have edited the query to be:

 merge INTO PROMOTIONS3 tgt

     using (

     SELECT PROMID

           ,xmlquery('/Promotion/MultibuyGroup/MMGroupID'

             passing xmlparse(document DEPTS)

             returning content

                      ) as new_mmg

       FROM PROMS WHERE PROMID  = 'EMP35Level1'

            ) src

       ON (tgt.ID = src.PROMID)

       when matched then update

       SET tgt.xml =

          xmlserialize(document

            xmlquery(

              'copy $d := .

               modify(

               delete nodes  $d/Promotion/MultibuyGroup/MMGroupID,

               insert node $new_mmg as last into  $d/Promotion/MultibuyGroup,

               replace value of node  $d/Promotion/LastUpdated with current-dateTime())

               return $d'

               passing xmlparse(document tgt.xml)

                      ,src.new_mmg as "new_mmg"

               returning content

                    )

             no indent

                        )

            ,last_updated = (SELECT SYSDATE FROM dual)

            ,row_updated = (SELECT ( SYSDATE - To_date('01-01-1970 00:00:00','DD-MM-YYYY HH24:MI:SS') ) *  24 * 60  * 60 * 1000 FROM dual) ;

So almost correct, except I need

 <LastUpdated>2018-08-23T14:56:35+01:00</LastUpdated>

Not

  <LastUpdated>2018-11-09T11:53:10.591000+00:00</LastUpdated>

So I need to figure that out.

Cheers.

For syntax you should google for XQuery Update Facility.

An example

xmlquery(  

'copy $d := .  

     modify(         

         delete nodes  $d/Promotion/MMGroupID,

         replace value of node  $d/Promotion/LastUpdated with current-date(),

         insert node <node1>x</node1> as last into  $d/Promotion/MultibuyGroup,

         insert node <node2>x</node2> as last into  $d/Promotion/MultibuyGroup) 

      return $d 

'