Friday 24 June 2016

Delete a Value in a XML based on a Condition in SQL SERVER


In this post we are going to see how to delete a element from xml based on condition in SQL SERVER


Xml format:
<Emps>
  <Emp dept="HR">a</Emp>
  <Emp dept="HR" />
  <Emp dept="tech">R</Emp>
</Emps>



DECLARE @employeedata XML
DECLARE @deletecondition VARCHAR(20) = 'HR'

--xml
SELECT @employeedata = N'<Emps><Emp dept="HR">a</Emp><Emp dept="HR"></Emp><Emp dept="tech">R</Emp></Emps>'

SELECT @employeedata




--Deletion
SET @employeedata.modify('delete (//Emps/Emp[@dept=sql:variable("@deletecondition")])') -- 
delete all HR records


SET @employeedata.modify('delete (//Emps/Emp[@dept=sql:variable("@deletecondition")])[1]') -- delete first HR record

SELECT @employeedata



Output:
<Emps>
  <Emp dept="tech">R</Emp>
</Emps>





No comments:

Post a Comment