Reading XML Data Type into XmlDocument Using ADO.NET - SQL Server 2005 Tutorials
by David Hayden ( Florida .NET Developer )
In continuing this look into the new XML Data Type in SQL Server 2005:
eventually you will want to load this XML into an XmlDocument ( as an example ) using ADO.NET.
Continuing with this idea of a library of books as used in the first two examples above, we can load the index of each book into an XmlDocument using the following ADO.NET
private XmlDocument GetDocument(string ISBN)
{
XmlDocument document = new XmlDocument();
using (SqlConnection connection =
new SqlConnection(connectionString))
{
string sql = "SELECT BookIndex FROM Books
WHERE ISBN = @ISBN";
SqlParameter[] sqlParams = new SqlParameter[1];
sqlParams[0] =
new SqlParameter("@ISBN", SqlDbType.Char, 13);
sqlParams[0].Value = ISBN;
using (SqlCommand command =
connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = sql;
command.Parameters.AddRange(sqlParams);
connection.Open();
using (SqlDataReader dr =
command.ExecuteReader())
{
if (dr.Read())
document.Load(
dr.GetSqlXml(0).CreateReader());
}
connection.Close();
}
}
return document;
}
If we call this method using:
XmlDocument document = GetDocument("1-59059-512-2");
We get the following information in the document's InnerXml Property:
Now you can modify or display the XML information as well as save it back to the database.
Source: David Hayden ( Florida .NET Developer )
Filed: SQL Server 2005, ADO.NET 2.0