Retrieving the XML Data Type Column from Client
This section focuses on the out-of-process access to the XML data type from a client ASP.NET page. There are different ways to retrieve an XML data typed column using a SqlDataReader object. Using the methods of the SqlDataReader class, you can retrieve the XML data either as a string or as an SqlXml object. The next section starts by exploring the steps involved in retrieving the contents of an XML column as a string.
Retrieving the XML Data Type Column as a String
Listing 10-4 retrieves the contents of the XML column as a string value from a client ASP.NET page. To this end, it executes a simple select statement to retrieve the contents of the xml_data column based on the supplied id value. Finally it displays the retrieved XML data as a string value in a text box.
Listing 10-4: Retrieving an XML Data Type Column as a String
<%@ Page Language="C#" ValidateRequest="false" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data.Sql" %>
<%@ Import Namespace="System.Web.Configuration" %>
<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 = WebConfigurationManager.ConnectionStrings ["adventureWorks"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString)) {
SqlCommand command = conn.CreateCommand();
command.CommandText = "SELECT xml_data FROM Employee WHERE id = " +
ID.ToString(); SqlDataReader reader = command.ExecuteReader();
//Get the XML value as string string xmlValue = (string)reader.GetValue(O);
txtXmlData.Text = xmlValue;
else txtXmlData.Text = "No Value";
<html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server">
<title>Retrieving an XML data type column as a String</title> </head> <body>
<form id="form1" runat="server"> <div>
<asp:Label ID="lblID" Runat="server" Text="ID:" Width="134px"
Weight="19px"></asp:Label> <asp:TextBox ID="txtID" Runat="server"></asp:TextBox> <asp:Button ID="btnReadXml" Runat="server" Text="Read Xml"
Width="118px" Height="3 0px" OnClick="btnReadXml_Click" /> <br/><br/><br/>
<asp:Label ID="lblXmlData" Runat="server" Text="XML:"
Width="134px" Height="19px"></asp:Label> <asp:TextBox ID="txtXmlData" Runat="server" Width="3 98px" Height="123px" TextMode="MultiLine"></asp:TextBox> </div> </form> </body> </html>
In this example, you execute the simple select query through a call to the ExecuteReader() method. After the results are available in the SqlDataReader object, you then retrieve the XML column value as a string using the GetValue(). Figure 10-5 shows the output produced by requesting the page from the browser.
Now that you have understood how easy it is to extract the contents of an XML data type column as a string value, the next section focuses on the steps required for extracting the output as an SqlXml object.
|
3 Retrieving an XML data type column as a String - Microsoft Internet Explorer |
uro | ||||||||||||||||||||||||||||||||||||||||||||||||
|
File Edit View Favorites loo Is Help |
JJL | ||||||||||||||||||||||||||||||||||||||||||||||||
|
Q Back ||] /Jj pSwoh Favoritas 0 0* |
- ü íi 0 i | ||||||||||||||||||||||||||||||||||||||||||||||||
|
Address [tí] híip7/íoc5Íhost/Mj"Prqect$A^rox/Cha^er10/4_Ge1><mla5pM |
zl SE= |
Read Xml <employee xmlns^'http://www.wrox.com/books" i d= w2 "X name > Fr e á</nawe ></erop X o yee > Figure 10-5 Retrieving XML Data Type Column as an SqlXml object Before looking at the use of SqlXml object, it is important to get an overview of the properties and methods supported by the SqlXml object. The SqlXml class is a new class introduced with ADO.NET 2.0 and it represents the XML data retrieved from a database server. The SqlXml class is exposed by the System.Data.SqlTypes namespace. Table 10-3 discusses the properties of the SqlXml class that you are going to have to most likely work with.
Now that you have a brief overview of the SqlXml class, it is time to demonstrate an example. Listing 10-5 shows how the GetSqlXml() method of the SqlDataReader can be used to get reference to an SqlXml object. Subsequently it also discusses how to create an instance of the XmlReader object using the CreateReader() method of the SqlXml class. Listing 10-5: Accessing an XML Data Type Column as an SqlXml Object <%@ 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.Web.Configuration" %> <%@ Import Namespace="System.Data.SqlTypes" %> <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 = WebConfigurationManager.ConnectionStrings ["adventureWorks"].ConnectionString; using (SqlConnection conn = new SqlConnection(connString)) { SqlCommand command = conn.CreateCommand(); command.CommandText = "SELECT xml_data FROM Employee WHERE ID = " + ID.ToString(); SqlDataReader reader = command.ExecuteReader(); SqlXml sqlXmlValue = reader.GetSqlXml(O); XmlReader xmlReader = sqlXmlValue.CreateReader(); if (xmlReader.Read()) output.Text = Server.HtmlEncode(xmlReader.ReadOuterXml()); else output.Text = "No Value"; <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Accessing an XML data type column as an SqlXml object</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="btnReadXml" Runat="server" Text="Read Xml" Width="118px" Height="3 0px" OnClick="btnGetXml_Click" /> <br/><br/><br/> <asp:Literal runat="server" ID="output"/> </div> </form> </body> </html> As similar to the previous example, this example also utilizes the ExecuteReader() method of the SqlDataReader object to execute the select query. After executing the query, the code invokes the GetSqlXml() method of the SqlDataReader object to obtain reference to the SqlXml object. SqlXml sqlXmlValue = reader.GetSqlXml(O); The code then invokes the factory method named CreateReader() to get an XmlReader object from the SqlXml object. XmlReader xmlReader = sqlXmlValue.CreateReader(); Finally the ReadOuterXml() method of the XmlReader object is invoked to display the results onto the browser. if (xmlReader.Read()) output.Text = Server.HtmlEncode(xmlReader.ReadOuterXml()); Navigate to this page using the browser, enter in an appropriate id, and hit the command button. You should see an output similar to Figure 10-6.
Related posts
| |||||||||||||||||||||||||||||||||||||||||||||||
Post a comment