SQL Server 2005 - DML Usage on an untyped XML column

DML or Data Manipulation Language is a set of statements used to store, retrieve, modify, and erase data from a database. DML does the following actions:

Insert – Adding new data
Delete – Removing existing data
Update – Modify existing data with new data

In SQL Server 2005, Microsoft introduced a new data type called XML to handle XML documents. It also provided a set of statements to store, retrieve, modify and erase data from the XML data type.

In this article, I am going to illustrate how to use data manipulation language (DML) for untyped XML columns.

Step 1

Let us assume that we have a database named "test" and we have a table, MyXMLtable, in the database. Let us also assume that we have the following XML data stored in a table as shown below.

Execute the following statements.

USE [master]
GO
/****** Object:  Database [test]    Script Date: 08/24/2006 14:50:29 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'test')
DROP DATABASE [test]
go
Create database test
go
USE [test]
GO
/****** Object:  Table [dbo].[MyXMLTable]    Script Date: 08/24/2006 14:50:14 ******/
IF  EXISTS (SELECT * FROM sys.objects 
  WHERE object_id = OBJECT_ID(N'[dbo].[MyXMLTable]') AND type in (N'U'))
DROP TABLE [dbo].[MyXMLTable]
go
create table MyXMLTable (id int, xmldat XML)
go
insert into MyXMLTable select 1,'
<?Names of Cat?> 
<Root>
 <!-- How to name your cat -->
<Cats> 
 <AmericanShorthair color="Grey" Friendly="No">Tiger</AmericanShorthair>
 <PersianHimalayan>MeowMeow</PersianHimalayan>
 <JapaneseBobtail>Rainbow River</JapaneseBobtail>
</Cats>
</Root>
'
go
insert into MyXMLTable select 2,'
<?Names of Dog?> 
<Root>
 <!-- How to name your Dog -->
<Dog> 
 <GreyHound color="dottedwhite" Intelligence="None">oldie</GreyHound>
 <Pomeranian>Jackie</Pomeranian>
 <ShihTzu>Rainbow</ShihTzu>
</Dog>
</Root>
'
go

Step 2

Let us query all the names of the dogs with an id of 2, using the following query:

Use test
go
SELECT xmldat.query(' //Dog') FROM MyXMLTable  where id=2

You will get the results shown below.

<Dog><GreyHound color="dottedwhite" Intelligence="None">oldie</GreyHound>
   <Pomeranian>Jackie</Pomeranian><ShihTzu>Rainbow</ShihTzu></Dog>

Let us query all the names of the PersianHimalayan cats with an id of 1, using the following query.

Use test
go
SELECT xmldat.query(' //Cats/PersianHimalayan') FROM MyXMLTable  where id=1

You will get the results as shown below

<PersianHimalayan>MeowMeow</PersianHimalayan>

Step 3

Let us delete the PersianHimalayan from the XML data for the ID 1. In order to delete a part of data from a table row, we are going to use the UPDATE statement and when updating the column we are going to use a special statement, columnname.modify, with XML DML commands.

Use test
go
UPDATE MyXMLTable 
SET xmldat.modify('delete /Root/Cats/PersianHimalayan ') where id =1

Now let us query the PersianHimalayan cats using the following query.

Use test
go
SELECT xmldat.query(' //Cats/PersianHimalayan') FROM MyXMLTable  where id=1

It returns nothing.

Now let us query all of the cats using the following query.

Use test
go
SELECT xmldat.query(' //Cats') FROM MyXMLTable  where id=1

You will get the result shown below.

<Cats><AmericanShorthair color="Grey" Friendly="No">Tiger</AmericanShorthair><JapaneseBobtail>Rainbow River</JapaneseBobtail></Cats>

Step 4

Let us insert a new dog breed, Dalmatian, with the name "Mayliah," using the following statement.

Use test
go
UPDATE MyXMLTable 
SET xmldat.modify('insert <Dalmation>Mayliah</Dalmation> 
as first  
into   
(/Root/Dog)[1]') where id =2

Let us query all of the Dogs using the following query.

Use test
go
SELECT xmldat.query('//Dog') FROM MyXMLTable  where id=2

You will get the result shown below:

<Dog><Dalmation>Mayliah</Dalmation><GreyHound color="dottedwhite" Intelligence="None">oldie</GreyHound>
   <Pomeranian>Jackie</Pomeranian><ShihTzu>Rainbow</ShihTzu></Dog>

Note: The data is inserted as the first row in the XML data.

Let us insert a new cat breed, oriental, with the name Serena. However, this time we are going to insert this row between Americanshorthair and japanesebobtail.

Execute the following statement.

Use test
go
UPDATE MyXMLTable 
SET xmldat.modify('insert <Oriental>Serena</Oriental> 
before (/Root/Cats/JapaneseBobtail)[1] ') where id =1

Let us query all of the Cats using the following query.

Use test
go
SELECT xmldat.query(' //Cats') FROM MyXMLTable  where id=1

You will get the result shown below:

<Cats><AmericanShorthair color="Grey" Friendly="No">Tiger</AmericanShorthair>
   <Oriental>Serena</Oriental><JapaneseBobtail>Rainbow River</JapaneseBobtail></Cats>

Note: The data is inserted in between Americanshorthair and japanesebobtail.

Step 5

So far, we have seen how to insert and delete the data from the XML. Using the combination of delete and insert, we can update the data. This is the same in DML triggers. Microsoft also provided a replace value statement used for updating discrete values. The only discrete values possible in an XML document are the contents of a tag or the value of an attribute.

Let us update the attribute value of AmericanShortHair from Friendly="No" to Friendly="Yes."

Execute the following statement.

Use test
go
UPDATE MyXMLTable 
SET xmldat.modify('
  replace value of (/Root/Cats/AmericanShorthair[@color="Grey"]/@Friendly)[1]
  with "Yes" ') where id =1

Let us query all of the Cats using the following query:

Use test
go
SELECT xmldat.query(' //Cats') FROM MyXMLTable  where id=1

You will get the result shown below:

<Cats><AmericanShorthair color="Grey" Friendly="Yes">Tiger</AmericanShorthair><Oriental>Serena</Oriental><JapaneseBobtail>Rainbow River</JapaneseBobtail></Cats>

Let us update the attribute value of GreyHound from Intelligence="None"

To Intelligence ="Some."

Execute the following statement:

Use test
go
UPDATE MyXMLTable 
SET xmldat.modify('
  replace value of (/Root/Dog/GreyHound[@color="dottedwhite"]/@Intelligence)[1]
  with "Some" ') where id =2

Let us query all of the Cats using the following query:

Use test
go
SELECT xmldat.query(' //Dog') FROM MyXMLTable  where id=2

You will get the result shown below.

<Dog><Dalmation>Mayliah</Dalmation><GreyHound color="dottedwhite" Intelligence="Some">oldie</GreyHound>
   <Pomeranian>Jackie</Pomeranian><ShihTzu>Rainbow</ShihTzu></Dog>

Conclusion

This article has illustrated how to use data manipulation language (DML) for an untyped XML column.

» See All Articles by Columnist MAK