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