存在，仅删除如果 存在,sql,xml,plsql"/>
时间戳

首页 > 解决方案 > 仅删除行存在，仅删除如果 存在

sql - 仅删除行存在，仅删除如果 存在

问题描述

考虑下表有 2 行。

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

我的问题是：

<UpdateServices>如果 XML 只有节点（意思是不存在）<AddServices>，如何删除该行？<RemoveServices>和

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

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;

谢谢你。:)

标签： sqlxmlplsql

解决方案

如果 XML 只有节点<UpdateServices>（意思是不存在）<AddServices>，如何删除该行？<RemoveServices>

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

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

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()解决方案。

推荐阅读