Advertisements
Home > XML > Using DML with XML – UPDATE & DELETE (SQL Server)

Using DML with XML – UPDATE & DELETE (SQL Server)


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:

Advertisements
Categories: XML Tags: , , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: