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.

Example,

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

USE [ResultTracker]
GO
/****** Object: StoredProcedure [dbo].[GetStudentList] Script Date: 9/12/2020 9:47:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetStudentList]
AS
BEGIN
select * from dbo.student
END
GO

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

USE [ResultTracker]
GO
/****** Object: StoredProcedure [dbo].[DeactivateStudentList] Script Date: 9/12/2020 9:36:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeactivateStudentList]
@student XML
AS
BEGIN
DECLARE @id int
EXEC sp_xml_preparedocument @id OUTPUT, @student
SELECT * INTO #InactiveStudents
FROM OPENXML (@id, ‘/NewDataSet/Table’ , 2)
WITH(
sname VARCHAR(50),
sid int
)
EXEC sp_xml_removedocument @id
select IA.sid,IA.sname INTO #DeactivatedStudents from [dbo].Result_student PW
INNER JOIN #InactiveStudents IA ON(IA.sid=PW.sid) where active=1
UPDATE PW
SET PW.active=0
from [dbo].Result_student PW
INNER JOIN #InactiveStudents IA ON(IA.sid=PW.sid) where active=1
SELECT * FROM #DeactivatedStudents
END
GO

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

App.config

<?xml version=”1.0" encoding=”utf-8" ?>
<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>

Program.cs

using System;
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”]);
}
}
}
}
}
}

Result

Thank You, See you in the next article !!

You can reach out or connect to me over here,

LinkedIn: https://www.linkedin.com/in/vaibhav-bhapkar

Email: vaibhavbhapkar.medium@gmail.com

If you want some technical topics to be discussed with group of participants please raise a request on following link: http://myblogsenquiry.somee.com/