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]

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

USE [ResultTracker]

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" ?>

Program.cs

using System;

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/

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