CODEWITHSUNDEEP
sql-serverxml
Alexcei Shmakov
Alexcei Shmakov

Reputation: 2343

How to remove the specific xml element from xml by SQL

I have a xml text like below

<services> 
  <service class="DataBuffer.Modules.Contraparty.ContrapartyService, DataBuffer.Modules.Contraparty"> 
      <host maxMessageSize="104857600">
      </host> 
  </service> 
  <service class="DataBuffer.Modules.Goods.GoodsService, DataBuffer.Modules.Goods"> 
      <host maxMessageSize="104857600">
      </host>
   </service> 
   <...other service element....>
</services>

I am declaring it in the SQL

DECLARE @doc varchar(1000);   
SET @doc ='  
<services> 
  <service class="DataBuffer.Modules.Contraparty.ContrapartyService, DataBuffer.Modules.Contraparty"> 
     <host maxMessageSize="104857600">
     </host> 
  </service> 
  <service class="DataBuffer.Modules.Goods.GoodsService, DataBuffer.Modules.Goods"> 
     <host maxMessageSize="104857600">
     </host>
  </service> 
</services>;'

I need to remove the service XML element that has a class attribute whose value is

DataBuffer.Modules.Contraparty.ContrapartyService, DataBuffer.Modules.Contraparty

How do I remove this element from raw xml text by SQL? Is it possible?

Upvotes: 0

Views: 595

Answers (1)

Paweł Dyl
Paweł Dyl

Reputation: 9143

Use DELETE (XML DML):

DECLARE @doc xml='<services> 
  <service class="DataBuffer.Modules.Contraparty.ContrapartyService, DataBuffer.Modules.Contraparty"> 
     <host maxMessageSize="104857600">
     </host> 
  </service> 
  <service class="DataBuffer.Modules.Goods.GoodsService, DataBuffer.Modules.Goods"> 
     <host maxMessageSize="104857600">
     </host>
  </service> 
</services>';

SET @doc.modify('delete //service[@class="DataBuffer.Modules.Contraparty.ContrapartyService, DataBuffer.Modules.Contraparty"]')

Upvotes: 1

Related Questions