Stored Procedure with XML Parameter

In this article, we will look into how we can use XML parameters in Stored Procedure and what are benefits of it,

You might be thinking what kind of situations are there where we prefer to pass data as XML below are some of the scenarios,

1) Consider a scenario where you have table data in datatable / dataset and you want to perform some kind of operation on individual record so you have two approaches to solving this first one is you can loop till data exists in the datatable / dataset and perform some kind of operation on individual entry, another one is you can pass datatable / dataset data to stored procedure and work around that data.

2) Let’s consider one more situation where you want to perform to store selected data of dataset / datatable into the table in this case also you can pass your data as XML to the stored procedure.


We have two table student table and Result_student table now situation is you want to select data from the student table and if that entry is active in the Result_student table then deactivate it,

Step 1: Design tables and add test data into it

We created two tables called dbo.student and dbo.Result_student as bellows,

dbo.student table
dbo.Result_student table

Step 2: Creating SP for fetching student list from dbo.student table as below

GO/****** Object: StoredProcedure [dbo].[GetStudentList] Script Date: 9/12/2020 9:47:35 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[GetStudentList]ASBEGINselect * from dbo.studentENDGO

Step 3: Creating SP to read XML compare it with dbo.ResultTable to check activated ids and then deactivate that

GO/****** Object: StoredProcedure [dbo].[DeactivateStudentList] Script Date: 9/12/2020 9:36:38 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[DeactivateStudentList]@student XMLASBEGINDECLARE @id intEXEC sp_xml_preparedocument @id OUTPUT, @studentSELECT * INTO #InactiveStudentsFROM OPENXML (@id, ‘/NewDataSet/Table’ , 2)WITH(sname VARCHAR(50),sid int)EXEC sp_xml_removedocument @idselect IA.sid,IA.sname INTO #DeactivatedStudents from [dbo].Result_student PWINNER JOIN #InactiveStudents IA ON(IA.sid=PW.sid) where active=1UPDATE PWSET [dbo].Result_student PWINNER JOIN #InactiveStudents IA ON(IA.sid=PW.sid) where active=1SELECT * FROM #DeactivatedStudentsENDGO

Step 4: Create a Console application to Get the list of deactivated students and passing to SP for deactivation as follows


<configuration><appSettings><add key=”connString” value=”data source=DESKTOP-UPBRTCM; initial catalog=ResultTracker; Integrated Security=True;”/></appSettings><startup><supportedRuntime version=”v4.0" sku=”.NETFramework,Version=v4.5" /></startup></configuration>


using System.Collections.Generic;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Text;using System.Threading.Tasks;namespace TestXml{class Program{static void Main(string[] args){SqlConnection conn;string DbConnectionString = ConfigurationSettings.AppSettings[“connString”].ToString();DataSet ds = new DataSet();using (conn = new SqlConnection(DbConnectionString)){SqlCommand sql_cmnd = new SqlCommand(“dbo.GetStudentList”, conn);sql_cmnd.CommandType = CommandType.StoredProcedure;SqlDataAdapter da = new SqlDataAdapter(sql_cmnd);da.Fill(ds);string XML = ds.GetXml();DataTable dt = new DataTable();SqlCommand sql_Cmnd2 = new SqlCommand(“dbo.DeactivateStudentList”, conn);sql_Cmnd2.CommandType = CommandType.StoredProcedure;sql_Cmnd2.Parameters.AddWithValue(“@student”, SqlDbType.Xml).Value = XML;SqlDataAdapter newDA = new SqlDataAdapter(sql_Cmnd2);newDA.Fill(dt);if (dt.Rows.Count > 0){foreach (DataRow dr in dt.Rows){Console.WriteLine(dr[“sid”] + “ “ + dr[“sname”]);}}}}}}


Thank You, See you in the next article !!

You can reach out or connect to me over here,



If you want some technical topics to be discussed with group of participants please raise a request on following link:

Technical Speaker | Computer Engineer | Full Stack Web Developer | ML Enthusiast | * Knowledge Shared = Knowledge² *