CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
One of the excellent features of the next release of SQL Server, known as SQL Server 2005, is its deep XML integration with the SQL Server database engine. This integration goes well beyond the simple relational-to-XML mapping layer that SQL Server 2000 provided. SQL Server 2005 will feature a native XML data type that will allow you to store native XML data in SQL Server itself.
Once you store XML data, you then can perform operations such as querying or updating it from ADO.NET 2.0, XML indexing, executing queries using a new XQuery language, and so on. This article explains how to work with the XML data type column in SQL Server 2005. Along the way, you also will see how to read and write values into the XML columns from ADO.NET 2.0.
Introducing XML Data Type
SQL Server 2005 introduces a native data type called XML. By using this feature, you can create a table with one or more columns of type XML besides relational columns. These XML values are stored in an internal format as large binary objects (BLOB), which enable the XML model to support document order and recursive structures. Because of the seamless integration of the XML data type with SQL Server’s storage and programming models, you can query and update XML documents and even write joins between XML data and relational data in your database. SQL Server 2005 uses the same query engine and optimizer for querying XML data as it does for relational data, radically enhancing your ability to work with XML data.
With the XML data typed columns, you also can specify an XML Schema Definition (XSD) schema that restricts the XML stored in the column or makes it variable to the vocabulary described in the schema.
Using XML Data Type
The XML data type (specified as XML) is a built-in data type just like varchar, int, and others. You use it the way you’d use any other SQL Server data type. Columns, variables, parameters, and values that functions return can all use the XML data type.
You can create a table that has an XML column by using the following code:
CREATE TABLE XmlTest( ID int NOT NULL, XmlDesc xml NOT NULL) GO
Although the XML data type is a built-in data type, it also functions like a user-defined data type (UDT) by providing several methods that let you query and update data stored in an XML variable or column. You can use these methods to query, modify, and obtain scalar values from an XML document that’s stored in a variable, column, or parameter.
For example, you can declare an XML variable named CustXml:
DECLARE @CustXml xml
You can declare a stored procedure that takes an XML document as a parameter:
CREATE PROCEDURE GetData
@CustXml xml
AS
--- Stored Procedure Code goes here
GO
Now that you understand how to declare XML data type columns, variables, and parameters, let’s study the code that is required to read and update values in an XML data type column from ADO.NET.
Saving Values into an XML Data Type Column
Now that you have a clear understanding of the XML data type in SQL Server 2005, you can create an ASP.NET page that saves information into an XML data type column by using the values entered by the user. For the purposes of this example, create a new Web site by selecting File->New Web Site from the Visual Studio 2005 menu. In the New Web Site dialog box, click Browse and make sure Local IIS is used to store your files. Figure 1 displays the screen you’ll see.
Figure 1: Create a New Web Site with Visual Studio 2005
Once you specify the virtual directory information in the Choose Location dialog box, click Open to bring up the New Web Site dialog box and click OK. Before creating the page, you must first create a table that the user will populate with the values he or she enters in the ASP.NET page. To create the SQL Server table, select Server Explorer from the View menu. In the Server Explorer, right-click on the Data Connections node and select Add Connection from the context menu. This will bring up the Connection Properties dialog box, in which you will need to specify the connection information. Once you specify the connection information, click OK. Now right-click on the Tables node in the Server Explorer and select Add New Table from the context menu. In the table definition screen, enter the table information shown in Figure 2.
Figure 2: Table Definition Screen Where Table Information Is Entered
After specifying the columns, save the table definition as XmlTest. As you can see, the XmlTest table has two columns: an ID column that represents a unique identifier and an XML column that stores XML-based content. Now that you have created the table, create a new ASP.NET page named XmlDataTypeSave.aspx by selecting Add New Item from the Web Site menu. Once the page is created, modify the code in the page to look like the following:
<%@ Page Language="C#" ValidateRequest="false" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data.Sql" %>
<%@ Import Namespace="System.Xml" %>
<%@ Import Namespace="System.Data.SqlTypes" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
void btnSave_Click(object sender, EventArgs e)
{
int ID = Convert.ToInt32(txtID.Text);
string xmlValue = txtXmlDesc.Text;
//Get the connection string from the web.config file
string connString =
System.Configuration.ConfigurationSettings.
ConnectionStrings["TestDBConnectionString"].
ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Insert XmlTest(ID, XmlDesc)
Values(@FirstCol, @SecondCol)";
//Set value of parameters
SqlParameter firstColParameter =
cmd.Parameters.Add("@FirstCol",
SqlDbType.Int);
firstColParameter.Value = ID;
SqlParameter secondColParameter =
cmd.Parameters.Add("@SecondCol", SqlDbType.Xml);
secondColParameter.Value = new SqlXml(new
XmlTextReader(xmlValue, XmlNodeType.Document, null));
//Execute update and close connection
cmd.ExecuteNonQuery();
}
Response.Write("Saved values successfully");
}
</script>
<html >
<head runat="server">
<title>Xml Data Type Save Demonstration</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="lblID" Runat="server" Text="ID:"
Width="134px" Height="19px"></asp:Label>
<asp:TextBox ID="txtID" Runat="server"></asp:TextBox>
<br/><br/><br/>
<asp:Label ID="lblXmlDesc" Runat="server" Text="Xml:"
Width="134px" Height="19px"></asp:Label>
<asp:TextBox ID="txtXmlDesc" Runat="server" Width="308px"
Height="82px" TextMode="MultiLine"></asp:TextBox>
<br/><br/><br/><br/>
<asp:Button ID="btnSave" Runat="server" Text="Save Values"
Width="118px" Height="30px"
OnClick="btnSave_Click" />
</div>
</form>
</body>
</html>
