SQL Server 存储过程解析XML传参

最新推荐文章于 2023-03-06 14:16:00 发布

__HelloWorld__

最新推荐文章于 2023-03-06 14:16:00 发布

阅读量7.9k

点赞数

CC 4.0 BY-SA版权

文章标签： .net sql server xml openxml

本文链接：https://blog.csdn.net/kangkanglou/article/details/37529981

本文介绍了一个用于更新数据库中HDWRSUMS表记录状态的存储过程，并展示了如何使用.NET框架进行调用。该存储过程通过解析传入的XML数据来更新对应记录的articleid和updatedate字段。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、定义存储过程

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[UpdateHDWRSUMSStatus]
 -- Add the parameters for the stored procedure here
 --<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
 --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
 @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 <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
 
 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格式如下

<Root>
<Record PROTSENO="PROTSENO" LOANSQNO="LOANSQNO" articleid="articleid">
</Record>>
</Root>