Microsoft & .NET.NETWork with XML Data Type in SQL Server 2005 from ADO.NET 2.0

Work with XML Data Type in SQL Server 2005 from ADO.NET 2.0

Developer.com 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>

The resulting page has two label controls, two textbox controls, and a button control. The Click event of the button saves the information to the database. Let’s examine the code inside the Click event. To start with, the ID and the XML string values that the user enters are stored in local variables:

int ID = Convert.ToInt32(txtID.Text);
         string xmlValue = txtXmlDesc.Text;

After that, you retrieve the connection string from the web.config file by using the new ConnectionStrings collection class:

      //Get the connection string from the web.config file
      string connString =
         System.Configuration.ConfigurationSettings.
ConnectionStrings["TestDBConnectionString"].ConnectionString;

For the preceding code to work, you need to have the connection string stored in the web.config file as follows:

<connectionStrings>
<add name="TestDBConnectionString"
     connectionString="server=(local)SqlExpress;
database=Test; Integrated Security=SSPI" />
</connectionStrings>

If the web.config file is not there, you can add one by selecting Add New Item from the Web Site menu and selecting Web Configuration File from the list of templates.

Once you have the connection string, you then can open the connection to the database by using the Open method of the SqlConnection object:

using (SqlConnection conn = new SqlConnection(connString))
      {
         conn.Open();

Then, you create a new SqlCommand object by invoking the CreateCommand method of the SqlConnection object. Next, you set the CommandText property of the SqlCommand object to an appropriate value:

SqlCommand cmd  = conn.CreateCommand();
cmd.CommandText = "Insert XmlTest(ID, XmlDesc)
                  Values(@ID, @XmlDesc)";

After that, you create the necessary parameters and then set their values:

//Set value of parameters
SqlParameter firstColParameter =
   cmd.Parameters.Add("@ID", SqlDbType.Int);
firstColParameter.Value = ID;
SqlParameter secondColParameter =
   cmd.Parameters.Add("@XmlDesc", SqlDbType.Xml);
secondColParameter.Value =
   new SqlXml(new XmlTextReader(xmlValue, XmlNodeType.Document
                                null));

To set the value for the XmlDesc parameter, create an object of type SqlXml. To the constructor of the SqlXML object, you supply an XmlReader object as an argument. The XML value that the user enters is supplied as an argument to the constructor of the XmlTextReader object.

Finally, execute the SQL query using the ExecuteNonQuery method of the SqlCommand object.

   //Execute update and close connection
   cmd.ExecuteNonQuery();
}
Response.Write("Saved values successfully");

Executing the preceding code will result in the output shown in Figure 3.

Figure 3: The Result from Executing Your SQL Query

In the Figure 3 screen, when you enter values for the ID and the XML columns and click the Save Values button, the entered values will be saved in the database.

Reading Values from an Xml Data Type Column

So far, you have seen how to write values into an XML data type column. Now you will see how to retrieve that XML value and then display that in an ASP.NET page. You can accomplish this by using either of the following two methods:

  1. Using GetValue method: In this method, you use the GetValue method of the SqlDataReader object and convert the returned value into an XML string representation.
  2. Using GetSqlXML method: In this method, you invoke the GetSqlXML method of the SqlDataReader object, which returns a SqlXML object.

The following sections look at both of these methods in detail.

Using the GetValue method

In the first method, you use the GetValue method to retrieve the XML data from the table. For the purposes of this example, create a new ASP.NET page named XmlDataTypeRead.aspx and modify its code 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 btnReadXml_Click(object sender, EventArgs e)
   {
      int ID = Convert.ToInt32(txtID.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 command = conn.CreateCommand();
         command.CommandText = "SELECT XmlDesc FROM XmlTest
                               WHERE ID = " + ID.ToString();
         SqlDataReader reader = command.ExecuteReader();
         if (reader.Read())
         {
            //Get the XML value as string
            string xmlValue = (string)reader.GetValue(0);
            txtXmlDesc.Text = xmlValue;
         }
         else
            txtXmlDesc.Text = "No Value";
      }
   }
</script>

<html  >
<head runat="server">
   <title>Xml Data Type Read Demonstration</title>
</head>
<body>
   <form id="form1" runat="server">
   <div>
      <asp:Label ID="lblID" Runat="server" Text="Enter ID:"
                 Width="134px" Height="19px"></asp:Label>
      <asp:TextBox ID="txtID" Runat="server"></asp:TextBox>
      <asp:Button ID="btnSave" Runat="server" Text="Read Xml"
                  Width="118px" Height="30px"
                 OnClick="btnReadXml_Click" />
      <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>
   </div>
   </form>
</body>
</html>

The preceding code is very similar to the previous example except that in this instance you execute a select statement as opposed to an insert statement. You append the Select statement with the ID entered by the user in the textbox. Then, you execute the select statement by invoking the ExecuteReader method of the SqlCommand object. Next, you retrieve the XML value by invoking the GetValue method of the SqlDataReader object and typecasting the returned value to a string. Finally, you display the XML in a textbox.

When you execute the preceding code, you will see an output that is somewhat similar to Figure 4.

Figure 4: Output from New ASP.NET Page XmlDataTypeRead.aspx

In the screen shown in Figure 4, you enter an ID value and click on the Read XML button to retrieve the XML column value from the database.

Using GetSqlXml Method

For the second method, you can use the GetSqlXML method to retrieve the XML data from the table. In this example, create a new ASP.NET page named XmlDataTypeGetXml.aspx and modify its code 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 btnGetXml_Click(object sender, EventArgs e)
   {
      int ID = Convert.ToInt32(txtID.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 command = conn.CreateCommand();
         System.Text.StringBuilder builder = new
            System.Text.StringBuilder();
         command.CommandText = "SELECT XmlDesc FROM XmlTest
                               WHERE ID = " + ID.ToString();
         SqlDataReader reader = command.ExecuteReader();
         if (reader.Read())
         {
            SqlXml sqlXmlValue = reader.GetSqlXml(0);
            XmlReader xmlReader = sqlXmlValue.CreateReader();
            //Loop through all the nodes in the retrieved XML
            while (xmlReader.Read())
            {
               builder.Append(xmlReader.ReadString());
               builder.Append("  ");
            }
            txtXmlDesc.Text = builder.ToString();
         }
         else
            txtXmlDesc.Text = "No Value";
      }
   }
</script>

<html  >
<head runat="server">
   <title>Xml Data Type Get Xml Demonstration</title>
</head>
<body>
   <form id="form1" runat="server">
   <div>
      <asp:Label ID="lblID" Runat="server" Text="Enter ID:"
                   Width="134px" Height="19px"></asp:Label>
      <asp:TextBox ID="txtID" Runat="server"></asp:TextBox>
      <asp:Button ID="btnSave" Runat="server" Text="Read Xml"
                    Width="118px" Height="30px"
      OnClick="btnGetXml_Click" />
      <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>
   </div>
   </form>
</body>
</html>

As with the previous example, this one also executes the select query by executing the ExecuteReader method of the SqlCommand object. However, it differs from the previous example in that it retrieves the value of the XML column in the form of a SqlXML object by using the GetSqlXML method. The SqlXML object represents the XML data retrieved from the database. Once you have the SqlXML object, you then can invoke the CreateReader method of the SqlXML object to get an XmlReader object. After that, the code loops through the XmlReader object (by invoking its Read method) and appends its contents to a StringBuilder object. Finally, you display the XML in a textbox.

When you execute the preceding code, you will see an output that is somewhat similar to Figure 5.

Figure 5: Output from New ASP.NET Page XmlDataTypeGetXml.aspx

In the screen shown in Figure 5, you enter an ID value and click on the Read XML button to retrieve the XML column value from the database. As you can see, the textbox displays only the node values contained in the retrieved XML.

Develop XML-Enabled Applications Easily

As you can see, working with the XML data type from ADO.NET 2.0 is very easy. The new ADO.NET 2.0 features will definitely help increase the productivity of developers. The features discussed in this article represent just the tip of the iceberg, but they should help you get a jump on this new feature. Once you get familiar with this approach of storing native XML inside SQL Server and then retrieving it from the code, you will have a rich set of techniques that can go a long way in making the development of XML-enabled applications a breeze.

Download the Code

To download the accompanying source code for this article, click here.

About the Author

Thiru Thangarathinam has six years of experience in architecting, designing, developing, and implementing applications using object-oriented application development methodologies. He also possesses a thorough understanding of the software life cycle (design, development, and testing). He holds several certifications, including MCAD for .NET, MCSD, and MCP. Thiru is an expert with ASP.NET, .NET Framework, Visual C# .NET, Visual Basic .NET, ADO.NET, XML Web services, and .NET Remoting. Thiru also has authored numerous books and articles. Contact him at thiruthangarathinam@yahoo.com.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories