将 XML 值指定为参数

如果查询需要值为 XML 字符串的参数，你可以使用 SqlXml 数据类型的实例提供该值。 真的没有任何窍门；SQL Server 中的 XML 列接受参数值的方式与其他数据类型完全相同。

示例

以下控制台应用程序在 AdventureWorks 数据库中创建新表。 新表包括名为“SalesInfo”的 SalesID 列和一个名为 “SalesInfo”的 XML 列。

备注

AdventureWorks安装 SQL Server 时，默认不会安装示例数据库。 可以通过运行 SQL Server 安装程序来安装它。

该示例准备一个 SqlCommand 对象以在新表中插入行。 保存的文件提供了SalesInfo列所需的XML数据。

若要创建运行示例所需的文件，请在与项目相同的文件夹中创建一个新的文本文件。 将文件命名为 MyTestStoreData.xml。 在记事本中打开该文件，然后复制并粘贴以下文本：

<StoreSurvey xmlns="http://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>

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 "..."
    End Function
End Module

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 "...";
    }
}

请参阅

• SqlXml
• SQL Server 中的 XML 数据
• ADO.NET 概述