TSQL If its sub-elements contain a given value, delete the XML node - Child, contain, delete, element, given, if, Node, TSQL, value, XML

XML storage can have zero or more nodes under

You need to query to delete any node of

and =’2 Church Lane’

Start XML:

declare @t table (storage xml);
insert into @t (storage)< br />select'
 
 Smith
 John
 
 
 1 Church Lane
 

 
 2 Church Lane
 

 
 3 Church Lane
 

 
 ';

The generated XML:


 
 Smith 
 John
 
 
 1 Church Lane
 

 
 3 Church Lane

Found:

update @t
SET storage.modify('
 delete (/Person/Addresses/Address[AddressLine = "2 Church Lane"])
')

XML storage can have zero or more nodes under
Need to be queried Delete any node of

and its =’2 Church Lane’

Start XML:

declare @t table (storage xml);
insert into @t (storage)
select'
 
 Smith
  John
 
 
 1 Church Lane
 

 
 2 Church Lane
 

 
 3 Church Lane
 
< br /> 
';

The generated XML:


 < Fullname />
 Smith< /Surname>
 John
 
 
 1 Church Lane
 

 
 3 Church Lane

Found:

update @t
SET storage.modify('
 delete (/Person/Addresses/Address[AddressLine = "2 Church Lane"])
')

Leave a Comment Cancel reply