SQL XML 資料行值 (ADO.NET)
SQL Server 2005 支援新的 xml 資料型別，開發人員可以使用 SqlCommand 類別的標準行為擷取包含此型別的結果集。 如同擷取任意資料行一樣，您可以擷取 xml 資料行 (例如，擷取至 SqlDataReader)，但是如果您要以 XML 的型式來使用資料行的內容，則必須使用 XmlReader。
範例
下列主控台應用程式會從 AdventureWorks 資料庫中的 Sales.Store 資料表，選取兩個資料列 (每個資料列包含一個 xml 資料行) 至 SqlDataReader 執行個體中。 針對每個資料列，可使用 SqlDataReader 的 GetSqlXml 方法讀取 xml 資料行的值。 該值儲存在 XmlReader 中。 請注意，如果您要將內容設為 SqlXml 變數，則必須使用 GetSqlXml 而不是 GetValue 方法；GetValue 會以字串的形式傳回 xml 的值。
.gif "注意事項") 注意事項 |
|---|
當您安裝 SQL Server 2005 時，預設不會安裝 AdventureWorks 範例資料庫。您可以藉由執行 SQL Server 安裝程式來安裝它。 |
' 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
// 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 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.
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;
}
}
}