Valeurs des colonnes SQL XML
SQL Server prend en charge le type de données xml et les développeurs peuvent extraire des ensembles de résultats incluant ce type à l’aide d’un comportement standard de la classe SqlCommand. Une colonne xml peut être récupérée comme n’importe quelle colonne (dans un SqlDataReader, par exemple), mais si vous souhaitez utiliser le contenu de la colonne au format XML, vous devez utiliser un XmlReader.
Exemple
L’application console suivante sélectionne deux lignes contenant chacune une colonne xml dans la table Sales.Store de la base de données AdventureWorks pour une instance de SqlDataReader. Pour chaque ligne, la valeur de la colonne xml est lue à l’aide de la méthode GetSqlXml de SqlDataReader. La valeur est stockée dans un XmlReader. Notez que vous devez utiliser GetSqlXml plutôt que la méthode GetValue si vous souhaitez définir le contenu sur une variable SqlXml ; GetValue renvoie la valeur de la colonne xml sous la forme d’une chaîne.
Notes
L’exemple de base de données AdventureWorks n’est pas installé par défaut quand vous installez SQL Server. Vous pouvez l’installer en exécutant le programme d’installation de 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