如果仅<节点1>存在则删除行，仅删除<节点1>如果<节点2> <节点3>存在

Question

考虑下表中的2行。

ID_NUM      | GENERAL_DATA
0100        | <...><UpdateServices><AddServices><RemoveServices><...>
0200        | <...><UpdateServices><...>

我的问题是：

如果XML只有节点<UpdateServices> (意味着没有<AddServices>和<RemoveServices> )，该如何删除该行？和

如果存在<AddServices>和<RemoveServices>，如何从XML中删除节点<UpdateServices>？

CREATE TABLE SAMPLE_TABLE AS
  select '0100' as id_num,
  XMLTYPE('<TRX>
  <DATA>
      <Request APIType="null">
          <SubscriberIdsInfo>
              <ExternalId>
                  <ExternalId>0100</ExternalId>
              </ExternalId>
              <SubscriberId>
                  <SubscrNumber/>
              </SubscriberId>
          </SubscriberIdsInfo>
          <UpdateServices>
              <Soc>ABC</Soc>
              <ServiceAgreementSequenceNo/>
              <DealerCode/>
              <DeployMode/>
              <EffectiveDate>2019-10-16T00:00:00</EffectiveDate>
              <ExpirationDate/>
              <OfferInstanceId/>
          </UpdateServices>
          <AddServices>
              <Soc>ABC1</Soc>
              <ServiceAgreementSequenceNo/>
              <DealerCode/>
              <DeployMode/>
              <EffectiveDate>2018-10-16T00:00:00</EffectiveDate>
              <ExpirationDate/>
              <OfferInstanceId/>
          </AddServices>
          <RemoveServices>
              <Soc>ABC2</Soc>
              <ServiceAgreementSequenceNo/>
              <DealerCode/>
              <DeployMode/>
              <EffectiveDate>2017-10-16T00:00:00</EffectiveDate>
              <ExpirationDate/>
              <OfferInstanceId/>
          </RemoveServices>
          <SubParameters>
              <Name>PoolID</Name>
              <Values>POOL0100</Values>
              <EffectiveDate>2014-10-16T14:08:37</EffectiveDate>
              <ExpirationDate/>
          </SubParameters>
          <ActivityInfo/>
      </Request>
  </DATA>
  </TRX>') general_data from dual;


 insert into SAMPLE_TABLE (id_num,general_data)
  select  '0200' as id_num, 
  XMLTYPE('<TRX>
  <DATA>
      <Request APIType="null">
          <SubscriberIdsInfo>
              <ExternalId>
                  <ExternalId>0200</ExternalId>
              </ExternalId>
              <SubscriberId>
                  <SubscrNumber/>
              </SubscriberId>
          </SubscriberIdsInfo>
          <UpdateServices>
              <Soc>ABC</Soc>
              <ServiceAgreementSequenceNo/>
              <DealerCode/>
              <DeployMode/>
              <EffectiveDate>2019-10-16T00:00:00</EffectiveDate>
              <ExpirationDate/>
              <OfferInstanceId/>
          </UpdateServices>
          <SubParameters>
              <Name>PoolID</Name>
              <Values>POOL0200</Values>
              <EffectiveDate>2014-10-16T14:08:37</EffectiveDate>
              <ExpirationDate/>
          </SubParameters>
          <ActivityInfo/>
      </Request>
  </DATA>
  </TRX>') general_data from dual;


commit;

谢谢。:)

Answer 1

如果XML只有节点(意味着不存在<AddServices>和<RemoveServices> )，如何删除行<UpdateServices>？

delete from sample_table X
where xmlExists('//UpdateServices' passing X.general_data)
    and not xmlExists('//AddServices|//RemoveServices' passing X.general_data)
;

如果<UpdateServices>和都存在，如何从XML中删除 <AddServices>的节点？

update sample_table X
set X.general_data = deleteXml(X.general_data, '//UpdateServices')
where xmlExists('//AddServices' passing X.general_data)
    and xmlExists('//RemoveServices' passing X.general_data)
;

注：我相信后者可以通过XQuery更有效地完成，但由于我(仍然)不懂该语言，我只能提供deleteXml()解决方案。