如何在Oracle中删除XML中的节点？

Question

我想从Oracle的XML中删除一个节点。我在删除节点时遇到了一个问题：

下面是我的XML：

    </TRB_TRX>
<DATA>
        <Request APIType="null">
            <SubscriberIdsInfo>
                <ExternalId>
                    <ExternalId>8034204058</ExternalId>
                </ExternalId>
                <SubscriberId>
                    <SubscrNumber/>
                </SubscriberId>
            </SubscriberIdsInfo>
            <Services>
                <Soc>88371664</Soc>
                <ServiceAgreementSequenceNo/>
                <DealerCode/>
                <DeployMode/>
                <EffectiveDate>2014-10-16T00:00:00</EffectiveDate>
                <ExpirationDate/>
                <OfferInstanceId/>
            </Services>
            <Services>
                <Soc>911143659</Soc>
                <ServiceAgreementSequenceNo/>
                <DealerCode/>
                <DeployMode/>
                <EffectiveDate>2014-10-16T00:00:00</EffectiveDate>
                <ExpirationDate/>
                <OfferInstanceId/>
            </Services>
            <Services>
                <Soc>42132</Soc>
                <ServiceAgreementSequenceNo/>
                <DealerCode/>
                <DeployMode/>
                <EffectiveDate>2014-10-16T00:00:00</EffectiveDate>
                <ExpirationDate/>
                <OfferInstanceId/>
            </Services>
            <SubParameters>
                <Name>PoolID</Name>
                <Values>B287024769280MDVCPOOL</Values>
                <EffectiveDate>2014-10-16T14:08:37</EffectiveDate>
                <ExpirationDate/>
            </SubParameters>
            <ActivityInfo/>
        </Request>
    </DATA>
</TRB_TRX>

我想删除下面的内容

    <Services>
                <Soc>88371664</Soc>
                <ServiceAgreementSequenceNo/>
                <DealerCode/>
                <DeployMode/>
                <EffectiveDate>2014-10-16T00:00:00</EffectiveDate>
                <ExpirationDate/>
                <OfferInstanceId/>
            </Services>
            <Services>  

和

    <Services>
                    <Soc>88371664</Soc>
                    <ServiceAgreementSequenceNo/>
                    <DealerCode/>
                    <DeployMode/>
                    <EffectiveDate>2014-10-16T00:00:00</EffectiveDate>
                    <ExpirationDate/>
                    <OfferInstanceId/>
                </Services>
                <Services>

我的最终输出应该如下所示

    </TRB_TRX>
        <DATA>
            <Request APIType="null">
                <SubscriberIdsInfo>
                    <ExternalId>
                        <ExternalId>8034204058</ExternalId>
                    </ExternalId>
                    <SubscriberId>
                        <SubscrNumber/>
                    </SubscriberId>
                </SubscriberIdsInfo>
                <Services>
                    <Soc>911143659</Soc>
                    <ServiceAgreementSequenceNo/>
                    <DealerCode/>
                    <DeployMode/>
                    <EffectiveDate>2014-10-16T00:00:00</EffectiveDate>
                    <ExpirationDate/>
                    <OfferInstanceId/>
                </Services>
                <SubParameters>
                    <Name>PoolID</Name>
                    <Values>B287024769280MDVCPOOL</Values>
                    <EffectiveDate>2014-10-16T14:08:37</EffectiveDate>
                    <ExpirationDate/>
                </SubParameters>
                <ActivityInfo/>
            </Request>
        </DATA>
    </TRB_TRX>

我正在使用下面的SQL查询

UPDATE trb1_sub_errs SET general_data = deleteXML(general_data,'//DATA/Request/Services[1]') where SUB_TRX_ID=1242403029;

下面是我得到的错误

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR

Answer 1

我看不到任何错误。唯一的区别是我在查询中没有任何谓词。

SQL> CREATE TABLE trb1_sub_ers AS
  2  select XMLTYPE('<TRB_TRX>
  3  <DATA>
  4          <Request APIType="null">
  5              <SubscriberIdsInfo>
  6                  <ExternalId>
  7                      <ExternalId>8034204058</ExternalId>
  8                  </ExternalId>
  9                  <SubscriberId>
 10                      <SubscrNumber/>
 11                  </SubscriberId>
 12              </SubscriberIdsInfo>
 13              <Services>
 14                  <Soc>88371664</Soc>
 15                  <ServiceAgreementSequenceNo/>
 16                  <DealerCode/>
 17                  <DeployMode/>
 18                  <EffectiveDate>2014-10-16T00:00:00</EffectiveDate>
 19                  <ExpirationDate/>
 20                  <OfferInstanceId/>
 21              </Services>
 22              <Services>
 23                  <Soc>911143659</Soc>
 24                  <ServiceAgreementSequenceNo/>
 25                  <DealerCode/>
 26                  <DeployMode/>
 27                  <EffectiveDate>2014-10-16T00:00:00</EffectiveDate>
 28                  <ExpirationDate/>
 29                  <OfferInstanceId/>
 30              </Services>
 31              <Services>
 32                  <Soc>42132</Soc>
 33                  <ServiceAgreementSequenceNo/>
 34                  <DealerCode/>
 35                  <DeployMode/>
 36                  <EffectiveDate>2014-10-16T00:00:00</EffectiveDate>
 37                  <ExpirationDate/>
 38                  <OfferInstanceId/>
 39              </Services>
 40              <SubParameters>
 41                  <Name>PoolID</Name>
 42                  <Values>B287024769280MDVCPOOL</Values>
 43                  <EffectiveDate>2014-10-16T14:08:37</EffectiveDate>
 44                  <ExpirationDate/>
 45              </SubParameters>
 46              <ActivityInfo/>
 47          </Request>
 48      </DATA>
 49  </TRB_TRX>') general_data from dual;

Table created.

SQL>
SQL> UPDATE trb1_sub_ers SET general_data = deleteXML(general_data, '//DATA/Request/Services[1]');

1 row updated.

SQL>

因此，检查SUB_TRX_ID的数据类型，它是字符串，因为您正在比较数字。