共用方式為

指定 XML 值做為參數 (ADO.NET)

  • 發行項

如果查詢需要值為 XML 字串的參數，則開發人員可以使用 SqlXml 資料型別的執行個體提供該值。 這其實沒什麼訣竅；SQL Server 2005 中的 XML 資料行接受參數值的方式與其他資料型別完全相同。

範例

下列主控台應用程式會在 AdventureWorks 資料庫中建立新的資料表。 新資料表包括名為 SalesID 的資料行及名為 SalesInfo 的 XML 資料行。

注意事項注意事項

當您安裝 SQL Server 2005 時，預設不會安裝 AdventureWorks 範例資料庫。您可以藉由執行 SQL Server 安裝程式來安裝它。

該範例準備了 SqlCommand 物件，以在新資料表中插入資料列。 已儲存的檔案為 SalesInfo 資料行提供必要的 XML 資料。

若要建立執行範例所需的檔案，請在與您專案相同的資料夾中建立新的文字檔。 將其命名為 MyTestStoreData.xml。 在 [記事本] 中開啟該檔案，然後複製並貼上下列文字：

<StoreSurvey xmlns="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey">
  <AnnualSales>300000</AnnualSales>
  <AnnualRevenue>30000</AnnualRevenue>
  <BankName>International Bank</BankName>
  <BusinessType>BM</BusinessType>
  <YearOpened>1970</YearOpened>
  <Specialty>Road</Specialty>
  <SquareFeet>7000</SquareFeet>
  <Brands>3</Brands>
  <Internet>T1</Internet>
  <NumberEmployees>2</NumberEmployees>
</StoreSurvey>

[Visual Basic]

Imports System
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Xml

Module Module1
    Sub Main()

        Using connection As SqlConnection = New SqlConnection(GetConnectionString())
        connection.Open()

        ' Create a sample table (dropping first if it already
        ' exists.)
        Dim commandNewTable As String = _
         "IF EXISTS (SELECT * FROM dbo.sysobjects " & _
         "WHERE id = object_id(N'[dbo].[XmlDataTypeSample]') " & _
         "AND OBJECTPROPERTY(id, N'IsUserTable') = 1) " & _
         "DROP TABLE [dbo].[XmlDataTypeSample];" & _
         "CREATE TABLE [dbo].[XmlDataTypeSample]( " & _
         "[SalesID] [int] IDENTITY(1,1) NOT NULL, " & _
         "[SalesInfo] [xml])"

        Dim commandAdd As New _
         SqlCommand(commandNewTable, connection)
        commandAdd.ExecuteNonQuery()

        Dim commandText As String = _
         "INSERT INTO [dbo].[XmlDataTypeSample] " & _
           "([SalesInfo] ) " & _
           "VALUES(@xmlParameter )"

        Dim command As New SqlCommand(commandText, connection)

        ' Read the saved XML document as a 
        ' SqlXml-data typed variable.
        Dim newXml As SqlXml = _
         New SqlXml(New XmlTextReader("MyTestStoreData.xml"))

        ' Supply the SqlXml value for the value of the parameter.
        command.Parameters.AddWithValue("@xmlParameter", newXml)

        Dim result As Integer = command.ExecuteNonQuery()
        Console.WriteLine(result & " row was added.")
        Console.WriteLine("Press Enter to continue.")
        Console.ReadLine()
    End Using
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

[C#]

using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Data.SqlTypes;

class Class1
{
    static void Main()
    {
        using (SqlConnection connection = new SqlConnection(GetConnectionString()))
       {
        connection.Open();
        //  Create a sample table (dropping first if it already
        //  exists.)

        string commandNewTable = 
            "IF EXISTS (SELECT * FROM dbo.sysobjects " + 
            "WHERE id = " +
                  "object_id(N'[dbo].[XmlDataTypeSample]') " + 
            "AND OBJECTPROPERTY(id, N'IsUserTable') = 1) " + 
            "DROP TABLE [dbo].[XmlDataTypeSample];" + 
            "CREATE TABLE [dbo].[XmlDataTypeSample]( " + 
            "[SalesID] [int] IDENTITY(1,1) NOT NULL, " + 
            "[SalesInfo] [xml])";
        SqlCommand commandAdd = 
                   new SqlCommand(commandNewTable, connection);
        commandAdd.ExecuteNonQuery();
        string commandText = 
            "INSERT INTO [dbo].[XmlDataTypeSample] " + 
            "([SalesInfo] ) " + 
            "VALUES(@xmlParameter )";
        SqlCommand command = 
                  new SqlCommand(commandText, connection);

        //  Read the saved XML document as a 
        //  SqlXml-data typed variable.
        SqlXml newXml = 
            new SqlXml(new XmlTextReader("MyTestStoreData.xml"));
        
        //  Supply the SqlXml value for the value of the parameter.
        command.Parameters.AddWithValue("@xmlParameter", newXml);

        int result = command.ExecuteNonQuery();
        Console.WriteLine(result + " row was added.");
        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=true;" +
        "Initial Catalog=AdventureWorks; ";
    }
}

請參閱

參考

SqlXml

概念

SQL Server 中的 XML 資料 (ADO.NET)