Stored Procedure with XML Parameter

dbo.student table
dbo.Result_student table
USE [ResultTracker]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
USE [ResultTracker]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 PW.active=0from [dbo].Result_student PWINNER JOIN #InactiveStudents IA ON(IA.sid=PW.sid) where active=1SELECT * FROM #DeactivatedStudentsENDGO
<?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>
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”]);}}}}}}

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Vaibhav Bhapkar

Vaibhav Bhapkar

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