1、定义存储过程
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[UpdateHDWRSUMSStatus]
-- Add the parameters for the stored procedure here
-- = ,
-- =
@xml xml
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
-- SELECT ,
DECLARE @xmlHandle int
EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @xml
Update hdwrsums
Set updatedate = getdate(),
articleid = modified.articleid
from (
SELECT *
FROM OPENXML (@xmlHandle, '/Root/Record',1)
WITH (PROTSENO varchar(34),
LOANSQNO varchar(3),
articleid int) ) as modified
Where hdwrsums.PROTSENO = modified.PROTSENO and hdwrsums.LOANSQNO = modified.LOANSQNO
EXEC sp_xml_removedocument @xmlHandle
RETURN
END
2、.NET调用存储过程
public static void ToUpdateHDWRSUMSStatus(string xmlstr)
{
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings["DBConnectionString"].ToString()))
{
connection.Open();
System.Data.SqlClient.SqlTransaction trans = connection.BeginTransaction();
System.Data.SqlClient.SqlCommand testcmd = new System.Data.SqlClient.SqlCommand();
testcmd.Connection = connection;
testcmd.Transaction = trans;
try
{
testcmd.CommandType = CommandType.StoredProcedure;
testcmd.CommandText = "UpdateHDWRSUMSStatus";
testcmd.Parameters.Add("@xml", SqlDbType.VarChar, -1).Value = xmlstr;
testcmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception exception)
{
trans.Rollback();
throw exception;
}
finally
{
connection.Close();
}
}
}
3、xml格式如下
>