SQL 2005/2008 - XML Type : modify method

September 13, 2010 by Christoff Truter SQL  

The modify method (when using the XML Type) allow developers to update/insert/remove specific nodes within the XML field.

Lets have a quick look at how to use this method.

First of all we're going to create a little play table - a table containing cars and a xml field containing the different colours we get for the car.

CREATE TABLE [dbo].[cars](
	[carID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[title] [varchar](255) NOT NULL,
	[colours] [xml] NULL
)

Some data (consider binding your xml type to a schema)

INSERT INTO cars(title, colours)
VALUES('VW', '<colours>
		<colour>Red</colour>
	</colours>')

Insert a new node at the end of the list (substitute last with first in order to insert a node at the top of the list)

UPDATE cars
SET colours.modify('insert element colour {"Green"} 
					as last
					into (/colours)[1]')
WHERE carID = 1

Insert a new node after a specific node (substitute after with before in order to insert the node before the specific node)

Example 1

UPDATE cars
SET colours.modify('insert element colour {"Lavendar"} 
					after (/colours/colour)[2]')
WHERE carID = 1

Example 2

UPDATE cars
SET colours.modify('insert element colour {"Brown"} 
					after (/colours/colour[.="Green"])[1]')
WHERE carID = 1

Example 3 (Inserting multiple nodes)

UPDATE cars
SET colours.modify('insert 
			(
			element colour {"Purple"}, 
			element colour {"Pink"}
			)
				as last
			into (/colours)[1]')
WHERE carID = 1

Example 4 (Doesn't work in SQL 2005)

DECLARE @colours XML
SET @colours = '<colour>Orange</colour><colour>White</colour>'

UPDATE cars
SET colours.modify('insert 
			(
			sql:variable("@colours")
			)
			as last
			into (/colours)[1]')
WHERE carID = 1

Update a specific node - in this case Green with DarkGreen.

UPDATE cars
SET colours.modify('replace value of (//colour[.="Green"])[1]
					with "DarkGreen"')
WHERE carID = 1

Delete specific node(s)

Example 1

UPDATE cars
SET colours.modify('delete (//colour[.="Blue"])')
WHERE carID = 1

Example 2 (Delete a number of nodes - this case all the nodes that contain green)

UPDATE cars
SET colours.modify('delete (//colour[contains(.,"Green")])')
WHERE carID = 1