SQL with Manoj

In my previous post we saw how we can INSERT records in a XML string. On this post we”ll see the UPDATE & DELETE operations by using same xml.modify() method.

DECLARE @xml XML

-- Taking the sample XML string we created in my previous post:
SET @xml=N'<Root>
  <stud id="0">
    <fname>Kanchan</fname>
    <lname>Pandey</lname>
    <class>10</class>
    <marks>90.5</marks>
  </stud>
  <stud id="1">
    <fname>Manoj</fname>
    <lname>Pandey</lname>
    <class>10</class>
    <marks>80.5</marks>
  </stud>
  <stud id="2">
    <fname>Saurabh</fname>
    <lname>Sharma</lname>
    <class>11</class>
    <marks>82.7</marks>
  </stud>
  <stud id="3">
    <fname>Rajesh</fname>
    <lname>Shah</lname>
    <class>11</class>
    <marks>70.3</marks>
  </stud>
</Root>'

> UPDATE marks of Manoj to 78.2 (currently 80.5):

SET @xml.modify('
	replace value of (/Root/stud[@id="1"]/marks/text())[1]
	with "78.2"')

SELECT @xml

> UPDATE marks of Rajesh to 78.7 stored in a variable:

DECLARE @marks FLOAT
SET @marks = 78.7

SET @xml.modify('
	replace value of (/Root/stud[@id="3"]/marks/text())[1]
	with sql:variable("@marks")')

SELECT @xml

> DELETE Saurabh record where id = 2:

SET @xml.modify('
	delete (/Root/stud[@id="2"])')

SELECT @xml

Final XML: