SQL XML 列值
SQL Server 支持 xml 数据类型，开发人员可以使用 SqlCommand 类的标准行为检索包括此类型的结果集。 可以检索 xml 列（例如，检索到 SqlDataReader 中），就像检索任何列一样；但若要以 XML 格式处理列内容，必须使用 XmlReader。
示例
以下控制台应用程序从 AdventureWorks 数据库的 Sales.Store 表中为 SqlDataReader 实例选择两行，每行包含一个 xml 列。 对于每一行，xml 列的值是使用 SqlDataReader 的 GetSqlXml 方法进行读取。 此值存储在 XmlReader 中。 请注意，若要将内容设置为 SqlXml 变量，必须使用 GetSqlXml，而不是 GetValue 方法；GetValue 以字符串形式返回 xml 列的值。
备注
默认情况下，在安装 SQL Server 时不安装 AdventureWorks 示例数据库。 可以通过运行 SQL Server 安装程序来安装它。
// Example assumes the following directives:
// using System.Data.SqlClient;
// using System.Xml;
// using System.Data.SqlTypes;
static void GetXmlData(string connectionString)
{
using (SqlConnection connection = new(connectionString))
{
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.
const string commandText =
"SELECT Demographics from Sales.Store WHERE " +
"CustomerID = 3 OR CustomerID = 4";
SqlCommand commandSales = new(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.
var 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)
{
var elementLocalName =
salesReaderXml.LocalName;
salesReaderXml.Read();
Console.WriteLine(elementLocalName + ": " +
salesReaderXml.Value);
}
}
countRow++;
}
}
}
' Example assumes the following directives:
' Imports System.Data.SqlClient
' Imports System.Xml
' Imports System.Data.SqlTypes
Private Sub GetXmlData(ByVal connectionString As String)
Using connection As SqlConnection = New SqlConnection(connectionString)
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
End Using
End Sub