July 28, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

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

  • September 10, 2004
  • By Thiru Thangarathinam
  • Send Email »
  • More Articles »

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 xmlns="http://www.w3.org/1999/xhtml" >
<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.



Click here for a larger image.

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 xmlns="http://www.w3.org/1999/xhtml" >
<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.



Click here for a larger image.

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.





Page 3 of 3



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel