Oracle XQuery删除、插入、更新

下面的所有操作我都可以在单独的操作中完成,但是由于我对XQuery还比较陌生,所以我正在努力研究如何一次执行多个操作,这将是很好的。

我正在尝试用来自另一列的一些XML数据更新XML列(这个XML来自一个包含两列的电子表格,促销编号和每个促销中的部门编号)。我把它放在一张桌子上,然后把下面的东西放在上面。

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
 );

创建一个包含PROMID列和XML列的表,如下所示(为了便于阅读,示例中只有一个MMGroup)。

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

当我运行下面的命令时,我可以成功地更新促销中的任何XML,其中ID列的值与上面创建的表中PROMID的值匹配。

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  
      )    ;

然而,我希望我要做的是从目标XML中删除任何现有的MMGCUPID节点(如果它们存在),然后用源XML中的所有节点替换它们。

目标xml中还有一个LastUpdated节点,我希望在更新时用SYSDATE更新它

还有两个独立的列LAST_UPDATED DATE和ROW_UPDATED NUMBER(20,0),这两个列具有更新的纪元时间,最好同时更新。

        <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>

自过帐后,已将查询编辑为:

 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) ;

几乎是对的,除了我需要

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

不是

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

所以我需要弄清楚。

干杯。