SQL Server 存储过程解析XML传参 参考方案

1、定义存储过程

-- =============================================
--定义存储过程
-- =============================================
CREATE PROCEDURE [dbo].[UpdateT]
-定义xml参数
@xml xml
AS
BEGIN
SET NOCOUNT ON;

DECLARE @xmlHandle int

--输出参数

EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @xml

SELECT MID,MName,MSex into #tmp
FROM OPENXML (@xmlHandle, '/Root/Mem',1)
WITH (MID  varchar(50),
MName varchar(50),
MSex varchar(10) ) 

Update Mem set MName=t.MName ,MSex=t.MSex from Mem m inner join #tmp t on m.MID=t.MID
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 = "UpdateT";
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>
<Mem MID="1"  MName="小赵" MSex="男">
</Mem>>
</Root>