"Improving XML Update Performance with SQL Server 2005"
This is the third and final article in a series of three
that look at how the latest version of Microsoft’s enterprise-level database, SQL
Server 2005, now offers great support and close integration with XML as a
data persistence format. This includes new ways to validate, store and query
XML documents that are stored within the database. SQL Server 2005 provides native
support for XML that can vastly improve application performance, while
supporting robust and safe multi-user access to the data contained within the
XML documents.
The topics we covered in the first article were:
- A brief overview of the way that SQL Server 2005 stores
XML documents and schemas
- How SQL Server 2005 provides support for querying and
manipulating XML documents
- A simple test application that allows you to experiment
with XQuery
In the second article of the series, we looked at:
- Extracting data from xml columns
- Comparing traditional XML data access approaches with
XQuery
- Combining XQuery and XSL-T
In this final article, we'll be exploring:
- Updating the contents of xml columns
- Comparing traditional XML update techniques with XQuery
- Using XQuery in a managed code stored procedure
About the Example Applications
As well as the XQuery Tester application
described in the first article, you can also use several other pages provided
in the downloadable samples to see XQuery in action. The full list of these
examples is:
- Reading XML Values the Traditional Way
- Using a Simple XQuery with an 'xml'
Column
- Comparing the Traditional Way and XQuery
- Using Parameters with an XQuery
- Combining XQuery and XSL-T
- Modifying an 'xml' Column with XQuery
- Comparing Traditional and XQuery Updates
- Using a Managed Code Stored Procedure in
SQL Server 2005
We won't be listing all of the code in this article, because
much of it uses standard ASP.NET and ADO.NET techniques to create the pages and
connect to the database. What we will concentrate on is how the examples
exploit the new XML data management features in SQL Server 2005. Many of the
examples use an un-typed xml column so that the queries are easier to assimilate (no namespace declaration
or prefixes are required). However, some processes (such as the modify method when
replacing values in the XML) require a typed column, and so the script we
provide creates two tables - one named Store with an un-typed xml column and one
named StoreTyped with a typed xml column. You'll see both used in the examples.
Traditional XML Update Techniques versus XQuery
In previous articles, we've only looked at how you can use
XQuery to select and extract data from an xml column in SQL Server 2005. The next area
of investigation is to see how we can use XQuery to update values in an xml column. This
example, "Modifying an 'xml' Column with XQuery", provides
three different SQL statements that use XQuery to update values in the Demographics column of both the typed and un-typed xml columns in the sample database.
Figure 1 shows the example page, and you can see these three
statements - we'll look at them in detail shortly. There is also a list of
customers generated by a GridView and a SqlDataSource control, allowing you to select a customer to see the update applied. If you
select the third statement, a text box is displayed where you can enter a new
value for the bank name.
Figure 1 - Updating an XML
document in SQL Server 2005 using XQuery
Each query in this example extracts the XML document from
the Demographics column of the row that was just updated, and the code in the page then displays
this document so that you can see the result. We'll show you these results as
we look at each of the update statements in turn. The code to execute each
query is shown in the next listing. It extracts the selected query statement
from the ASP.NET RadioButtonList control, creates a command, and adds to it a parameter for the customer ID -
taken from the selected row in the GridView control.
If the user selected the third statement, which updates the BankName element
within the XML document, the new value for this element is extracted from the TextBox control in
the page and used to set another parameter on the command. Then the query is
executed, and the updated XML document returned from the Demographics column is displayed in a Label control on the page. To make it easier to see the results, the code inserts
line breaks between each element in the XML so that it wraps in the page:
Using con As New SqlConnection(ConfigurationManager.ConnectionStrings
_
("XMLTestConnectionString").ConnectionString)
Dim cmd As New SqlCommand(optQType.SelectedValue,
con)
cmd.Parameters.Add(New SqlParameter("@custid",
Int32.Parse(grid1.SelectedDataKey.Value)))
If optQType.SelectedIndex = 2 Then
cmd.Parameters.Add(New SqlParameter("@newname",
txtNewName.Text))
End If
Try
con.Open()
Dim sResult As String = Server.HtmlEncode(cmd.ExecuteScalar())
lblMessage.Text = sResult.Replace("><",
"><br /><")
con.Close()
Catch ex As Exception
lblMessage.Text = ex.Message
End Try
End Using
The Query that Inserts a New Element
The first query we provide inserts a new
element into the XML document in the Demographics column. It
executes the XML-DML insert statement within a call to the modify method of the Demographics column, specifying that the XML fragment <newnode>Somevalue</newnode> should be inserted as the first child of the StoreSurvey root element:
UPDATE Store SET Demographics.modify('
insert <newnode>Some value</newnode>
as first into /StoreSurvey[1]
')
WHERE CustomerID = @custid
Figure 2 shows the result. You can see the new element in
the XML document at the top of the page:
Figure 2 - Inserting a new
element into an XML document in SQL Server 2005 using XQuery
This statement - and the delete statement shown next - access an
un-typed xml column in the Store table. If we were accessing a typed xml column, we would have to declare the
namespace of the elements in the document and add prefixes to the XPath. The update
statement in this case would be:
UPDATE Store SET Demographics.modify('
declare namespace x="http://testschemas/StoreSurvey";
insert <x:newnode>Some value</x:newnode>
as first into /x:StoreSurvey[1]
')
WHERE CustomerID = @custid
However, this will only work if the schema
for the column allows this node to be part of a valid document. In the StoreTyped table, the schema does not include this element, so the update
would fail with a validation error.
The Query that Deletes an Existing Element
The second statement removes all nodes
named newnode from the XML document in the Demographics column. It
executes the XML-DML delete statement within a call to the modify method of the Demographics column, specifying the XPath "//newnode".
Alternatively, we could have specified the full XPath "/StoreSurvey/newnode ":
UPDATE Store SET Demographics.modify('delete
//newnode') WHERE CustomerID = @custid
The result, when the same customer row is
selected in the GridView, shows that the newnode element has been
removed from the document:
Figure 3 - Deleting an existing
element from an XML document in SQL Server 2005 using XQuery
The Query that Updates an Existing Element
The previous two statements operate on an
un-typed xml column. However, the XML-DML replace statement used
in this XQuery statement can only be used on a typed column. This means that we
have to declare the namespace as referenced in the schema for this column, and
add the corresponding prefixes to the query. This is the third update
statement:
UPDATE StoreTyped SET Demographics.modify('
declare namespace x="http://testschemas/StoreSurvey";
replace value of (//x:BankName)[1] with xs:string(sql:variable("@newname"))
')
WHERE CustomerID = @custid
Notice how this takes an extra parameter
named @newname, which contains the new value for the BankName element, and we access this in the XQuery using the sql:variable function. To ensure that the value is of a type compatible with the
schema for the column, we cast it to a string type. Figure 4
shows the results of executing this query, and you can see the updated value
for the bank name in the XML document that is extracted from the Demographics column after the query has been executed.
Figure 4 - Updating the contents
of an element in an XML document in SQL Server 2005 using XQuery
Comparing XQuery Update Performance with the Traditional
Approach
In the previous article, we showed an
example that compares the performance of XQuery with the traditional approach
to extracting values from XML documents stored in a database. This next
example, "Comparing Traditional and XQuery Updates", does the
same kind of thing, but compares performance when updating values in the XML
documents stored an xml column.
The aim is to update the value of one
element within the XML document in the Demographics column for
all 700+ rows in the StoreTyped table. The example page offers three options:
- Update the value of the Internet element using the traditional DataSet approach
- Update the value of the Internet element using an individual XQuery on each row
- Update the value of the Internet element using an XQuery that process all rows in one go
The queries we use for each of these
options, and the code to execute them, are discussed next. To save space and
make the relevant parts easier to see, we've omitted some code that opens the
database connection, records the time taken for the process, displays the
results, and handles any errors. You can see all the code in the samples
available for download from http://www.daveandal.net/articles/sql2005-xquery/.
Updating the Document Using the Traditional Approach
The first option in the example page uses
the two SQL statements, shown in the next listing. A normal SELECT statement fetches a rowset containing the customer ID and the
contents of the Demographics column from every row in the table. An UPDATE statement is used to replace the content of the Demographics column with a new XML document for the row specified by the @custid parameter. Notice how we convert the string value that will be
provided by the @newxml parameter into an xml type before applying
it to the row:
SELECT CustomerID, Demographics FROM StoreTyped
UPDATE StoreTyped SET Demographics = CONVERT(xml,
@newxml) WHERE CustomerID = @custid
The code in the page goes through three
distinct stages when you execute this query. First it uses the SELECT statement to fetch the rowset containing all the rows from the StoreTyped table into a DataTable:
' fill a DataTable with the rows from the StoreTyped
table
da = New SqlDataAdapter(sql_trad_select,
con)
da.Fill(dt)
...
Then it iterates through the DataTable, updating the value of the node named Internet in the XML document stored in each row by loading it into an
instance of the XmlDocument class. Because this is a typed column, and the XML document
contains a namespace declaration, the code has to generate an XmlNamespaceManager instance for this document and add the namespace to it. This allows the SelectSingleNode method to locate the required element, and the value can be updated. Finally,
the updated XML document is extracted from the XmlDocument instance as a String using the OuterXml property,
and stored back in the DataTable row:
...
' iterate through the rows in the DataTable
Dim xd As New XmlDocument()
Dim xn As XmlNode
Dim xmlns As XmlNamespaceManager
Dim nav As XPathNavigator
For Each dr As DataRow In dt.Rows
' update the XML document in the
Demographics column
xd.LoadXml(dr("Demographics").ToString())
xmlns = New XmlNamespaceManager(xd.NameTable)
xmlns.AddNamespace("x",
"http://testschemas/StoreSurvey")
xn = xd.SelectSingleNode("//x:Internet",
xmlns)
nav = xn.CreateNavigator()
nav.SetValue("DSL")
dr("Demographics") = xd.OuterXml
Next
...
Now the code can generate the UpdateCommand required by the DataAdapter, and add the
two parameters required to it. These parameters reference the two columns in
the DataTable where the values will come from for each row as the update takes
place. Finally, a call to the Update method of the DataAdapter pushes the changes back into the database table:
...
' push the changes back into the database
Dim cmd As New SqlCommand(sql_trad_update,
con)
cmd.Parameters.Add(New SqlParameter("@newxml",
SqlDbType.VarChar, 1000, "Demographics"))
cmd.Parameters.Add(New SqlParameter("@custid",
SqlDbType.Int, 4, "CustomerID"))
da.UpdateCommand = cmd
iRows = da.Update(dt)
Figure 5 shows the result. You can see at
the bottom of the page that the code records the time taken for the complete process
(extracting and updating the rows), and displays this along with a count of the
number of rows that were updated. An average time for the 700+ rows using our
hardware setup was around 650 milliseconds.
Figure 5 - Updating the XML
documents in the StoreTyped table using the traditional approach
Updating the Document Using an XQuery for Each Row
The second option in this example uses an
XQuery to perform the updates on each row in the StoreTyped table. The
two queries it uses are shown in the next listing. The first simply returns the CustomerID from every row in the table. There is no need to extract the
contents of the Demographics column this time because we don't need to update it on the client.
Instead the update will be carried out by the second SQL statement, which
contains an XQuery that uses an XML-DML statement within the modify method of the Demographics column:
SELECT CustomerID FROM StoreTyped
UPDATE StoreTyped SET Demographics.modify('
declare namespace x="http://testschemas/StoreSurvey";
replace value of (//x:Internet)[1] with x:InternetType("T1")
')
WHERE CustomerID = @custid
To perform the updates, the code first
fills a DataTable with the rowset containing the Customer ID values:
' fill a DataTable with the rows from the StoreTyped
table
da = New SqlDataAdapter(sql_xquery_select,
con)
da.Fill(dt)
...
Then it iterates through the DataTable executing a command that applies the UPDATE statement to each
row in turn. Notice that the code opens the connection before iterating the
rows, and then closes it after all the updates are complete, to minimize
processing time:
...
' iterate through the rows in the DataTable
Dim cmd As New SqlCommand(sql_xquery_update,
con)
cmd.Parameters.Add(New SqlParameter("@custid",
SqlDbType.Int))
con.Open()
For Each dr As DataRow In dt.Rows
' iterate through the rows updating the
'xml' column in the
' database with an XQuery modify
statement
cmd.Parameters("@custid").Value
= dr("CustomerID")
iRows += cmd.ExecuteNonQuery()
Next
con.Close()
The result of selecting this option is
shown in Figure 6. The average time taken with out hardware setup was around
175 milliseconds, giving a saving of over 70%. This is due both to the
reduction in client-side processing that is required when using an XQuery, as
well as the saving in processing and bandwidth required to transport the XML
documents from the database to the client for updating, and then back to the
database. The setup that produced these results had the Web server and SQL
Server 2005 running on the same machine. When accessing a remote SQL Server,
you should see even greater improvements.
Figure 6 - Updating the XML
documents in the StoreTyped table using an XQuery on each row
Updating the Document Using a Single XQuery for All the
Rows
Both of the previous options have performed
the updates on one row at a time. In the case of the traditional approach, this
is the only way it can be done because the code replaces the complete XML
document in the Demographics column with an updated one. So the original document must be
extracted first, updated, and then pushed back into the table. In the second
option, there was no need to extract the XML document from the database because
the XQuery only updates one value in the document and so the rest of the
document is unchanged.
However, if you are applying the same
update to every row (as in this example), you might be able to create an XQuery
that can be executed against all the rows, automatically calculating the new
value for the element or attribute you want to update. In our simple example,
we update the Internet element to the same value in every row. Depending on what update
you what to carry out, you may be able to use the built-in functions of XPath,
or an XQuery that contains a custom function, to calculate the new value.
The example shown here uses the following
SQL statement to update all the rows in the StoreTyped table in one
operation. Basically, it is the same as the previous option, but does not
contain a WHERE clause and so the operation will be applied to all the rows in the
table:
UPDATE StoreTyped SET Demographics.modify('
declare namespace x="http://testschemas/StoreSurvey";
replace value of (//x:Internet)[1] with x:InternetType("56kb")
')
This means that it can be executed using
the following simple code:
' execute the single XQuery statement that
updates all the rows
Dim cmd As New SqlCommand(sql_xquery_update_all,
con)
con.Open()
iRows += cmd.ExecuteNonQuery()
con.Close()
However, the result (shown in Figure 7) doesn’t appear to
offer much advantage over the previous option where we updated each row in
turn. This is because, in effect, the process that SQL Server 2005 must carry
out is the same - except that it processes all the rows in response to one command,
rather than having to update each row for separate individual commands. Of
course, the results here are slewed by the fact that the database and client
(the Web server) are on the same machine. When a remote database is used,
network bandwidth will come into play and - depending on the loading of the
database - you should see a more significant improvement.
Figure 7 - Updating the XML
documents in the StoreTyped table using one XQuery on all the rows
Using XQuery in a Managed Code Stored Procedure
The final topic in this series of articles
is to investigate how we can use the new feature of SQL Server 2005 that allows
stored procedures to be written in managed code, when we are working with XML
data and XQuery. Included in the samples you can download from this article is
a Visual Studio 2005 project that implements a managed code stored procedure
named UpdateContactDetails.
Figure 8 shows this stored procedure as
installed in SQL Server 2005 - the lock on the icon indicates that it is a
managed code procedure implemented as an assembly (a DLL) stored within the
database. And, as you can see from Figure 8, the stored procedure takes a
single parameter that is a customer ID for one of the rows in the StoreTyped table.
Figure 8 - The UpdateContactDetails
managed code stored procedure in SQL Server 2005
You can open the sample project named ManagedCodeProc in Visual Studio 2005 and change the connection to point to your
own SQL Server 2005 database using the Database page of the Properties window (see Figure 9). Then compile and deploy the stored procedure
to the database using the commands on the Build menu in Visual
Studio.
Figure 9 - Changing the
connection to point to your own SQL Server 2005 database in Visual Studio 2005
After deploying the stored procedure, you must give the
account(s) that will access it EXECUTE permission - just as you have to with any stored
procedure. The project we provide in the samples includes a Test.sql script
that executes the following T-SQL statement. You can change the account name as
required:
GRANT EXECUTE ON UpdateContactDetails TO
[public]
The UpdateContactDetails Stored Procedure
The code to implement the UpdateContactDetails stored procedure is a normal .NET Class file. It
starts by importing the namespaces that are required to access the database, and
the namespace that contains the SqlProcedure() attribute (Microsoft.SqlServer.Server). This is
followed by the routine that actually implements the stored procedure, decorated with the SqlProcedure() attribute. You can see
from the code that the parameter visible in the SQL Server Management Studio
window (Figure 8) is declared as a parameter to the main routine:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
<SqlProcedure()> _
Public Shared Sub UpdateContactDetails(ByVal
CustomerID As Int32)
...
As the name suggests, this stored procedure
is designed to update a row in the Demographics column of
the StoreTyped table. The schema for the Demographics column
allows the XML documents stored in this column to include a ContactDetails element, however none of the rows contain this element by default.
This means that the code must discover if the XML document already contains
this element, and update it with the new value, or insert the complete element
and value if it is not already present. So the procedure declares three SQL
statements.
The XQuery Statements to Update the XML Documents
The first of the SQL statements for this
example uses the query method of the Demographics column to extract the values of existing elements, which we'll use to decide
what the new contact name will be:
SELECT CustomerID,
ContactName = Demographics.query('
declare namespace x="http://testschemas/StoreSurvey";
data(//x:ContactName)
'),
BusinessType = Demographics.query('
declare namespace x="http://testschemas/StoreSurvey";
data(//x:BusinessType)
'),
AnnualSales = Demographics.query('
declare namespace x="http://testschemas/StoreSurvey";
data(//x:AnnualSales)
')
FROM StoreTyped WHERE CustomerID = @custid
The second is an UPDATE statement that will replace the value of an existing ContactName element with the new value, passed to it via the parameter named @newname. As in previous examples, we have to specify a single node for the
target in the replace statement, and we cast the value of the parameter to a string type:
UPDATE StoreTyped SET Demographics.modify('
declare namespace x="http://testschemas/StoreSurvey";
replace value of (//x:ContactName)[1]
with xs:string(sql:variable("@newname"))
')
WHERE CustomerID = @custid
The third statement will be executed where
the XML document in the Demographics column does not currently
contain a ContactName element. It uses an XML-DML insert statement to
insert the new element, taking the value from the parameter named @newname - again casting it to a string type first. To comply with the
schema for this column, the ContactName element must
be the first child element in the document and so we specify asfirstinto and provide the root element as the target:
UPDATE StoreTyped SET Demographics.modify('
declare namespace x="http://testschemas/StoreSurvey";
insert <x:ContactName>{ xs:string(sql:variable("@newname"))
}</x:ContactName>
as first into (//x:StoreSurvey)[1]
')
WHERE CustomerID = @custid
Executing the XQuery Statements within the Stored Procedure
The main body of the stored procedure code
uses these three statements to update the ContactName element in
the row specified by the CustomerID value that is passed into the
stored procedure when it is executed. The first step is to create a connection
to the database, and for this the special value "contextconnection=true" is used for the
connection string with a standard SqlConnection class instance. Then the
code creates a command from this connection, and adds the @custid parameter
to it.
Next, as with ordinary client-side code, it opens the
connection, executes the query to get a SqlDataReader instance over the results,
and calls the Read method to access the first (and only) row. The existing values from the XML
document, returned by the first of the SQL statements we looked at earlier, are
stored in local variables and the SqlDataReader can be closed:
' create inproc connection using
SqlClient SqlConnection
Using con As New SqlConnection("context
connection=true")
' create a Command and get a DataReader
for the row
Dim cmd As SqlCommand = con.CreateCommand()
cmd.CommandText = sql_select
cmd.Parameters.Add(New SqlParameter("@custid",
CustomerID))
con.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
If dr.HasRows Then
dr.Read()
' get values from reader
Dim contactname As String = dr.GetString(1)
Dim businesstype As String = dr.GetString(2)
Dim annualsales As Int32 = Int32.Parse(dr.GetString(3))
dr.Close()
...
Now we decide what the new contact name
will be. This obviously depends on the business logic you need to implement,
and here we're taking some fairly arbitrary decisions based on the values in
the elements we extracted from the XML document. Notice that we also set a flag
to indicate if the row currently contains a ContactNamevalue. We'll
assume for the purposes of this example that the absence of a value means that
there is no ContactName element, though to be sure you would probably need to execute an exist method call to check that an empty element is not present in the
document:
...
' decide what new contact name will
be
Dim bExists As Boolean = (contactname
<> String.Empty)
Select Case businesstype
Case "BM"
If annualsales > 150000 Then
contactname = "Mike
Sharp"
Else
contactname = "Maria McCauly"
End If
Case "OS"
If annualsales > 10000 Then
contactname = "Arthur
Atkinson"
Else
contactname = "Christina
Wells"
End If
Case "SGS"
If annualsales < 150000 Then
contactname = "Mike
Sharp"
Else
contactname = "Kim
Wong"
End If
Case Else
contactname = "Jonathan
Kosygin"
End Select
...
The final section of code updates the XML
document in the Demographics column by executing one of the other two SQL statements we declared
earlier. If the ContactName element already exists, it executes the query that uses an XML-DML replace statement, while the query that uses an XML-DML insert statement is executed if the ContactName element does
not already exist:
...
' now ready to update the database
table row
' set Command to update SQL
statement and add parameter
' containing new value to pass to
the XQuery
If bExists Then
cmd.CommandText = sql_update
Else
cmd.CommandText = sql_insert
End If
cmd.Parameters.Add(New SqlParameter("@newname",
contactname))
cmd.ExecuteNonQuery()
End If
con.Close()
End Using
End Sub
End Class
Executing the UpdateContactDetails Stored Procedure
The example page that uses the UpdateContactDetails stored procedure first declares two queries. These consist of the
name of new stored procedure, and a SQL statement that is used simply to
extract the XML document from the Demographics column so
that you can see the results:
Dim sql_execute As String = "UpdateContactDetails"
Dim sql_select As String = "SELECT Demographics
FROM StoreTyped WHERE CustomerID = @CustomerID"
The page also contains an ASP.NET GridView and SqlDataSource, which together retrieve
and display a list of the customers in the StoreTyped table. Clicking on the Select button in
one of the rows in the GridView executes the routine named SelectCustomer in the page. This routine starts by creating a connection and command,
specifying the new managed code stored procedure, and adds a parameter to the
command that will pass the selected customer ID value into the stored
procedure. Then it opens the connection to the database, and executes the
stored procedure:
Sub SelectCustomer(ByVal sender As Object, ByVal
e As EventArgs)
Using con As New SqlConnection(ConfigurationManager.ConnectionStrings
_
("XMLTestConnectionString").ConnectionString)
Try
' create command and execute the
stored procedure
Dim cmd As New SqlCommand(sql_execute,
con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@CustomerID",
Int32.Parse(grid1.SelectedDataKey.Value)))
con.Open()
cmd.ExecuteNonQuery()
...
Once the stored procedure completes, the
code continues by using the SELECT statement
declared earlier to retrieve the XML document from the Demographics column in this row, and displays it in the page. An HTML line break is inserted between each element to make the
results easier to see:
...
' now extract the XML document from
the Demographics
' column in the same row and display
it in the page
cmd.CommandText = sql_select
cmd.CommandType = CommandType.Text
Dim sResult As String = Server.HtmlEncode(cmd.ExecuteScalar())
con.Close()
lblMessage.Text &= sResult.Replace("><",
"><br /><")
Catch ex As Exception
Try
con.Close()
Catch
End Try
lblMessage.Text = ex.Message
End Try
End Using
End Sub
Figure 10 shows the results. You can see that the stored
procedure inserted the ContactName element into the XML document, with (in this case) the value "Mike Sharp".
If you select other rows, you'll see that different names are inserted into the
new ContactName element.
Figure 10 - Executing the UpdateContactDetails
managed code stored procedure
Summary
In the previous two articles, we discussed the great new
features in SQL Server 2005 that make it easier to work with XML documents, and
also allows you to write more efficient data access code when you need to
persist, access and update XML. SQL Server 2005 is not the first database to
provide these features, but the combination of these new features and
integration with ADO.NET make it easy to take advantage of them, while vastly
reducing the amount of code you have to write. We also explored how XQuery
provides great performance and simplifies data access code requirements when
extracting data from XML documents stored in SQL Server 2005, and using it in a
variety of ways.
In this article, we delved deeper into XQuery and SQL Server
2005 XML features to see how you can improve performance when updating XML
documents stored in the database, and how you can use XQuery within managed
code stored procedures. You saw how you can execute XQuery statements that
update values in an XML document stored in both a typed and an un-typed xml column using
the XML-DML insert, delete and replace statements. You also saw the dramatic improvement in performance that results
from using XQuery when compared to traditional methods that extract the complete
XML document, update it, and then push it back into the database.
Finally, we looked at how you can create, compile and deploy
managed code stored procedures that use XQuery in a SQL Server 2005 database;
and how you can then execute these procedures. It is possible to write and
compile a managed code stored procedure without using Visual Studio 2005 (by
using one of the command-line .NET compilers), and deploy it using the T-SQL
statements that install an assembly in SQL Server 2005. However, Visual Studio
makes the whole process much easier, and allows you to set break-points and
debug the code while it is executing if required.
