TSQL If its sub-elements contain a given value, delete the XML node

October 21, 2021By Simo 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"])
')

Child, contain, delete, element, given, if, Node, TSQL, value, XML

WordPress database error: [Table 'yf99682.wp_s6mz6tyggq_comments' doesn't exist]
SELECT SQL_CALC_FOUND_ROWS wp_s6mz6tyggq_comments.comment_ID FROM wp_s6mz6tyggq_comments WHERE ( comment_approved = '1' ) AND comment_post_ID = 6000 ORDER BY wp_s6mz6tyggq_comments.comment_date_gmt ASC, wp_s6mz6tyggq_comments.comment_ID ASC

Leave a Comment Cancel reply