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>

SQL Server 預存程序解析XML傳參 參考方案