SQL Server 2005 XML DML ( XML Data Manipulation Language ) and the XML Data Type
by David Hayden ( SQL Server Developer )
In my previous post, XML Data Type XQuery XML Indexes XML Schema Collection and Http Endpoints in SQL Server 2005, I presented an example of using various new SQL Server 2005 XML features that allowed me to search the indexes of my vast collection of .NET books by keyword and return the book titles and appropriate page numbers of books that contained more information.
For example, if I typed in "ADO.NET", my winform application would call an XML Web Service ( provided via SQL Server HTTP Endpoints ) that would return a collection of books as follows:
The architecture looked as such:
XQuery in SQL Server 2005 Stored Procedure
The heart of the application was the use of XQuery in the FindBooksByKeyword Stored Procedure that 1) searched through the BookIndex XML Data Type Column looking for the appropriate keyword in each book's index and 2) returned the titles and page numbers where there was a match.
CREATE PROCEDURE dbo.FindBooksByKeyword
@Keyword varchar(25)
AS
BEGIN
SET NOCOUNT ON;
SELECT Title, BookIndex.value('(
/index/keyword[.=sql:variable("@Keyword")]
/@page)[1]','varchar(25)') as Page
FROM Books
WHERE BookIndex.exist('/index/keyword[
.=sql:variable("@Keyword")]') = 1
END
GO
XML DML ( XML Data Manipulation Language )
The above gave us a nice read-only type of application, but certainly there will come a time when we want to add, modify, and delete keywords in the index of each book.
The beauty here is that in order to modify each book's index we don't have to go through the labor of retrieving the entire index of each book, making modifications at the client, and then saving the modified index back to the database. We can instead use the XML Data Manipulation Language ( XML DML ) that allows us to modify the XML Data Type directly in a stored procedure.
XML DML is an extension of the XQuery language, which as defined by W3C, the XQuery language lacks the Data Manipulation (DML) part. XML DML provides a fully functional query and data-modification language that you can use against the xml data type.
XML DML adds the following case-sensitive keywords to XQuery:
- insert
- delete
- replace value of
SQL Server 2005 Stored Procedures
In addition to the original FindBooksByKeyword Stored Procedure, there are now 3 additional stored procedures to insert, modify, and delete keywords in the index of a book: InsertKeyword, ModifyKeyword, DeleteKeyword.
InsertKeyword Stored Procedure using XML DML
Inserting a keyword for a particular book, ISBN, is as simple as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[InsertKeyword]
@ISBN char(13),
@Keyword varchar(25),
@Page int
AS
BEGIN
UPDATE Books
SET
BookIndex.modify('insert
{ sql:variable("@Keyword") }
as last into (/index[1])')
WHERE ISBN = @ISBN
END
Here again we have the use of sql:variable to pull the contents of the @Keyword and @Page parameters into the modify XML DML command.
ModifyKeyword Stored Procedure using XML DML
This stored procedure replaces the current page number of a keyword for a particular book:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[ModifyKeyword]
@ISBN char(13),
@Keyword varchar(25),
@NewPage int
AS
BEGIN
UPDATE Books
SET
BookIndex.modify('replace value of (
/index/keyword[.=sql:variable("@Keyword")]
/@page)[1] with sql:variable("@NewPage")')
WHERE ISBN = @ISBN
END
DeleteKeyword Stored Procedure using XML DML
This is the stored procedure for deleting a keyword:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[DeleteKeyword]
@ISBN char(13),
@Keyword varchar(25)
AS
BEGIN
UPDATE Books
SET
BookIndex.modify('delete /index/keyword[
.=sql:variable("@Keyword")]')
WHERE ISBN = @ISBN
END
Abstracting the XML Data Type
If you look at all the stored procedures, you will notice they abstract exactly how the book index ( keywords and page numbers ) are stored for each book. It is only until you get into the stored procedure do you realize that XQuery, XPath, XML DML, and the XML Data Type are being exploited in SQL Server 2005.
The abstraction not only keeps the client applications from being concerned with XML, but also opens up the opportunity to modify how the information is being stored in case the current architecture proves to be less than ideal.
Conclusion
The new SQL Server 2005 XML Features are a pretty, pretty thing :) In this tutorial I showed how to make changes to the index of each book, stored as an XML Data Type, using the XML Data Manipulation Language ( XML DML ).
Source: David Hayden ( SQL Server Developer )
SQL Server 2005 Resources: