如何在数据库中批量插入包含XML类型列的行?

我正在尝试将行大容量插入数据库。这个表的一些列是xml类型的,这就是我面临的一些问题。

我试着用 SqlBulkCopy 为了实现这一点,请继续得到一个错误,即尽管我从未进行类型转换,但xelement不能转换为字符串。

这是我的桌子设计:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TasksQueue]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Type] [int] NOT NULL,
    [ProcessData] [xml] NOT NULL,
    [MetaData] [xml] NULL,
    [SubmittedBy] [int] NULL,
    [Status] [int] NOT NULL,
    [ResultData] [xml] NULL,
    [SubmittedDate] [datetime] NULL,
    [ProcessedDate] [datetime] NULL,
    [ProcessingTime] [varchar](28) NULL,
    [DistrictID] [int] NULL,
    [Name] [nvarchar](128) NULL,
 CONSTRAINT [PK_TasksQueue] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

我在C_中的代码:

public static void CreateBulkTasks(List<Task> BulkTasks)
{
        string connectionString = ConfigurationManager.AppSettings[AppSettingsKeys.QwizdomOnlineDBConnectionString];
        _obj = new object();

        lock (_obj)
        {
            DataTable tableMember = new DataTable();
            DataTable tableLicense = new DataTable();

            var x = BulkTasks[0].MetaData;

            tableMember.Columns.Add("Type", typeof(Int32));
            tableMember.Columns.Add("ProcessData", typeof(XElement));
            tableMember.Columns.Add("MetaData", typeof(XElement));
            tableMember.Columns.Add("SubmittedBy", typeof(Int32));
            tableMember.Columns.Add("Status", typeof(Int32));
            tableMember.Columns.Add("ResultData", typeof(XElement));
            tableMember.Columns.Add("SubmittedDate", typeof(DateTime));
            tableMember.Columns.Add("ProcessedDate", typeof(DateTime));
            tableMember.Columns.Add("ProcessingTime", typeof(string));
            tableMember.Columns.Add("DistrictID", typeof(Int32));
            tableMember.Columns.Add("Name", typeof(string));

            foreach (var task in BulkTasks)
            {
                tableMember.Rows.Add(
                    task.Type,
                    task.ProcessData,
                    task.MetaData,
                    task.SubmittedBy,
                    task.Status,
                    task.ResultData,
                    task.SubmittedDate,
                    task.ProcessedDate,
                    task.ProcessingTime,
                    task.DistrictID,
                    task.Name
                );
            }

            using (System.Data.SqlClient.SqlBulkCopy bulkCopyGroupMembers = new System.Data.SqlClient.SqlBulkCopy(connectionString))
            {
                bulkCopyGroupMembers.DestinationTableName = "[TasksQueue]";
                bulkCopyGroupMembers.ColumnMappings.Add("Type", "Type");
                bulkCopyGroupMembers.ColumnMappings.Add("ProcessData", "ProcessData");
                bulkCopyGroupMembers.ColumnMappings.Add("MetaData", "MetaData");
                bulkCopyGroupMembers.ColumnMappings.Add("SubmittedBy", "SubmittedBy");
                bulkCopyGroupMembers.ColumnMappings.Add("Status", "Status");
                bulkCopyGroupMembers.ColumnMappings.Add("ResultData", "ResultData");
                bulkCopyGroupMembers.ColumnMappings.Add("SubmittedDate", "SubmittedDate");
                bulkCopyGroupMembers.ColumnMappings.Add("ProcessedDate", "ProcessedDate");
                bulkCopyGroupMembers.ColumnMappings.Add("ProcessingTime", "ProcessingTime");
                bulkCopyGroupMembers.ColumnMappings.Add("DistrictID", "DistrictID");
                bulkCopyGroupMembers.ColumnMappings.Add("Name", "Name");
                bulkCopyGroupMembers.WriteToServer(tableMember);
    }
}

public class Task
{
    public int? ID { get; set; }
    public TasksQueueType Type { get; set; }
    public XElement ProcessData { get; set; }
    public XElement MetaData { get; set; }
    public int? SubmittedBy { get; set; }
    public string SubmittedByName { get; set; }
    public TasksQueueItemStatus Status { get; set; }
    public XElement ResultData { get; set; }
    public int? DistrictID { get; set; }
    public DateTime SubmittedDate { get; set; }
    public DateTime? ProcessedDate { get; set; }
    public TimeSpan? ProcessingTime { get; set; }
    public bool? HighPriority { get; set; }
    public string Name { get; set; }
}

我希望大容量数据可以一次插入到表中。相反,我得到这个错误:

在System.Data.dll中出现了“Stase.ValueStudio异常”类型的异常,但未在用户代码中处理

其他信息:无法将“system.xml.linq.xelement”类型的对象强制转换为“system.string”类型。