Modify the Value of a XML in SQL SERVER

In this post we are going to see how to modify a value in XML in SQL SERVER, Now below example will explain you how to change the value of an Hr to tech in an xml first tag.    [1]  Refers the occurrence of first element. 

       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>'

SET @employeedata.modify('replace value of (/Emps/Emp[@dept=("HR")]/@dept)[1] with "Tech"')

SELECT @employeedata

Output: 

<Emps>

  <Emp dept="Tech">a</Emp>

  <Emp dept="HR" />

  <Emp dept="tech">R</Emp>

           </Emps>