用xml作参数一次插入多条数据

1.存储过程方法：

declare @customers xml
set @customers='<cus bpNo=''15'' MNo=''商户ID1'' MName=''商户名称1'' /><cus bpNo=''15'' MNo=''商户ID2'' MName=''商户名称2'' />'

INSERT control.tb_ExternalEnterprise(BPNo,EnterpriseId,EnterpriseName)
SELECT R.c.value('@bpNo','CHAR(6)'),R.c.value('@MNo','VARCHAR(50)'),
       R.c.value('@MName','NVARCHAR(30)')
 FROM @customers.nodes('cus') R(c)

 

2.sql语句方法：

INSERT tb_ExternalEnterprise(BPNo,EnterpriseId,EnterpriseName)
SELECT b.*
 FROM (select cast('<cus bpNo=''15'' MNo=''商户ID1'' MName=''商户名称1''/>
     <cus bpNo=''15'' MNo=''商户ID2'' MName=''商户名称2'' />' as xml) a)t
cross apply(select R.c.value('@bpNo','CHAR(6)') as bpNo,
                   R.c.value('@MNo','VARCHAR(50)') as MNo,
                   R.c.value('@MName','NVARCHAR(30)') as MName
            from t.a.nodes('cus') R(c)) b

 

System.Text.StringBuilder sb = new System.Text.StringBuilder("'");
            for (int i = 0; i < count; i++)
            {
                sb.AppendFormat("<cus bpNo='{0}' />", i.ToString());//Guid.NewGuid().ToString());
            }
            sb.Append("'");

************************* SqlBulkCopy ********************************

private static long SqlBulkCopyInsert()
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();

            DataTable dataTable = GetTableSchema();
            string passportKey;
            for (int i = 0; i < count; i++)
            {
                passportKey = i.ToString();//Guid.NewGuid().ToString();
                DataRow dataRow = dataTable.NewRow();
                dataRow[0] = passportKey;
                dataTable.Rows.Add(dataRow);
            }

            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString);
            sqlBulkCopy.DestinationTableName = "Passport";
            sqlBulkCopy.BatchSize = dataTable.Rows.Count;
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            sqlConnection.Open();
            if (dataTable != null && dataTable.Rows.Count != 0)
            {
                sqlBulkCopy.WriteToServer(dataTable);
            }
            sqlBulkCopy.Close();
            sqlConnection.Close();

            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }