SQL Server stored procedure parsing XML pass-through reference scheme

1. Define Stored procedures

-- =============================================
--Defining stored procedures
-- =============================================
CREATE PROCEDURE [dbo]. [Updatet]
-Define XML parameters
@xml XML
As
BEGIN
SET NOCOUNT on;

DECLARE @xmlHandle int

--Output parameters

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 calling stored procedures

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. The XML format is as follows

<Root>
<mem mid= "1" mname= "Xiao Zhao" msex= "male" >
</Mem>>
</Root>

SQL Server stored procedure parsing XML pass-through reference scheme