Using XML Column Values in a DataReader
SQL Server 2005 supports the new xml data type, and developers can retrieve result sets including this type using standard behavior of the SqlCommand class. An xml column can be retrieved just as any column is retrieved (into a SqlDataReader, for example) but if you want to work with the content of the column as XML, you must use an XmlReader.
Example
The following console application selects two rows, each containing an xml column, from the Sales.Store table in the AdventureWorks database to a SqlDataReader instance. For each row, the value of the xml column is read using the GetSqlXml method of SqlDataReader. The value is stored in an XmlReader. Note that you must use GetSqlXml rather than the GetValue method if you want to set the contents to a SqlXml variable; GetValue returns the value of the xml column as a string.
Note
The AdventureWorks sample database is not installed by default when you install SQL Server 2005. You can install it by running SQL Server Setup.
[Visual Basic]
Imports System
Imports System.Data.SqlClient
Imports System.Xml
Imports System.Data.SqlTypes
Module Module1
Sub Main()
Dim connection As SqlConnection
connection = New SqlConnection(GetConnectionString())
connection.Open()
' The query includes two specific customers for simplicity's
' sake. A more realistic approach would use a parameter
' for the CustomerID criteria. The example selects two rows
' in order to demonstrate reading first from one row to
' another, then from one node to another within the xml
' column.
Dim commandText As String = _
"SELECT Demographics from Sales.Store WHERE " & _
"CustomerID = 3 OR CustomerID = 4"
Dim commandSales As New SqlCommand(commandText, connection)
Dim salesReaderData As SqlDataReader = _
commandSales.ExecuteReader()
' Multiple rows are returned by the SELECT, so each row
' is read and an XmlReader (an xml data type) is set to the
' value of its first (and only) column.
Dim countRow As Integer = 1
While salesReaderData.Read()
' Must use GetSqlXml here to get a SqlXml type.
' GetValue returns a string instead of SqlXml.
Dim salesXML As SqlXml = _
salesReaderData.GetSqlXml(0)
Dim salesReaderXml As XmlReader = salesXML.CreateReader()
Console.WriteLine("-----Row " & countRow & "-----")
' Move to the root.
salesReaderXml.MoveToContent()
' We know each node type is either Element or Text.
' All elements within the root are string values.
' For this simple example, no elements
' are empty.
While salesReaderXml.Read()
If salesReaderXml.NodeType = XmlNodeType.Element Then
Dim elementLocalName As String = _
salesReaderXml.LocalName
salesReaderXml.Read()
Console.WriteLine(elementLocalName & ": " & _
salesReaderXml.Value)
End If
End While
countRow = countRow + 1
End While
Console.WriteLine("Press Enter to continue.")
Console.ReadLine()
End Sub
Private Function GetConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrieve it from a configuration file.
Return "Data Source=(local);Integrated Security=SSPI;" & _
"Initial Catalog=AdventureWorks"
End Function
End Module
using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Data.SqlTypes;
class Class1
{
static void Main()
{
SqlConnection connection;
connection = new SqlConnection(GetConnectionString());
connection.Open();
// The query includes two specific customers for simplicity's
// sake. A more realistic approach would use a parameter
// for the CustomerID criteria. The example selects two rows
// in order to demonstrate reading first from one row to
// another, then from one node to another within the xml
// column.
string commandText =
"SELECT Demographics from Sales.Store WHERE "+
"CustomerID = 3 OR CustomerID = 4";
SqlCommand commandSales =
new SqlCommand(commandText, connection);
SqlDataReader salesReaderData =
commandSales.ExecuteReader();
// Multiple rows are returned by the SELECT, so each row
// is read and an XmlReader (an xml data type) is set to the
// value of its first (and only) column.
int countRow = 1;
while (salesReaderData.Read())
// Must use GetSqlXml here to get a SqlXml type.
// GetValue returns a string instead of SqlXml.
{
SqlXml salesXML =
salesReaderData.GetSqlXml(0);
XmlReader salesReaderXml = salesXML.CreateReader();
Console.WriteLine("-----Row "+ countRow+ "-----");
// Move to the root.
salesReaderXml.MoveToContent();
// We know each node type is either Element or Text.
// All elements within the root are string values.
// For this simple example, no elements
// are empty.
while (salesReaderXml.Read())
{
if (salesReaderXml.NodeType == XmlNodeType.Element)
{
string elementLocalName =
salesReaderXml.LocalName;
salesReaderXml.Read();
Console.WriteLine(elementLocalName+ ": "+
salesReaderXml.Value);
}
}
countRow = countRow+ 1;
}
Console.WriteLine("Press Enter to continue.");
Console.ReadLine();
}
private static string GetConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
return "Data Source=(local);Integrated Security=SSPI;" +
"Initial Catalog=AdventureWorks; ";
}
}