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.
