Sdílet prostřednictvím

Hodnoty pro sloupce SQL XML

  • Článek

SQL Server podporuje xml datový typ a vývojáři mohou načíst sady výsledků včetně tohoto typu pomocí standardního SqlCommand chování třídy. xml Sloupec lze načíst stejně jako jakýkoli sloupec (například do sloupceSqlDataReader), ale pokud chcete pracovat s obsahem sloupce jako XML, musíte použít XmlReader.

Příklad

Následující konzolová aplikace vybere dva řádky, z xmltabulky Sales.Store v databázi AdventureWorks do SqlDataReader instance. Pro každý řádek se hodnota xml sloupce čte pomocí GetSqlXml metody SqlDataReader. Hodnota je uložena v objektu XmlReader. Všimněte si, že místo metody je nutné použít GetSqlXmlGetValue , pokud chcete nastavit obsah na proměnnou SqlXml ; GetValue vrátí hodnotu xml sloupce jako řetězec.

Poznámka:

Ukázková databáze AdventureWorks není ve výchozím nastavení nainstalována při instalaci SQL Serveru. Můžete ho nainstalovat spuštěním instalačního programu SYSTÉMU 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

Viz také