xml是什么入参格式 存储过程_SQL Server 存储过程解析XML传参

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格式如下

>