Friday 24 June 2016

Insert an new Element in a XML using SQL SERVER


In this post we are going to see how to add a new element in a XML using SQL SERVER, Now we see how to insert an new element in the existing xml , in different positions.


        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


--Insertion
SET @employeedata.modify('insert <Emp>b</Emp>  as first into (/Emps)[1]')

-- or
SET @employeedata.modify('insert <Emp>b</Emp>  into (/Emps/Emp)[1]') -- insert as last tag
SELECT @employeedata

<Emps>
  <Emp>b<Emp>b</Emp></Emp>
  <Emp dept="HR">a</Emp>
  <Emp dept="HR" />
  <Emp dept="tech">R</Emp>
</Emps>







     

No comments:

Post a Comment