How to use XML in SQL Server and Oracle database Stored Procedure to create dynamic InClause for select query

How to use XML in SQL Server and Oracle database Stored Procedure to create dynamic InClause for select query Lots of time you have a requirement to get multiple rows from database, that time you have to create InClause and send it to stored procedure. Oracle you can pass Array to stored procedure and it will solve you purpose to create InClause inside stored procedure, (see article using link http://riteshk.blogspot.com/2009/03/how-to-send-array-as-input-parameter-to.html) But in SQL Server database you don't have any way to pass Array to stored procedure, only way available to use XML for this. Here I am describing the way to pass XML to create InClause in SQL server and Oracle stored procedure. XML format can be multiple types and based on that your stored procedure structure will change. Following code has been tested in following environments 1) .NET framework 2.0 2) Oracle 10g 3) SQL Express 4) Microsoft Enterprises Library 3.0 Sample Xml which you will pass it to Stored Procedure <ArrayList> <Values><Value>40415</Value></Values> <Values><Value>59556</Value> </Values> <Values> <Value>67900</Value></Values> </ArrayList> Oracle Stored Procedure CREATE OR REPLACE PROCEDURE Pass_Array_Oracle ( prmInClause IN XMLTYPE, prmOutCursor OUT SYS_REFCURSOR ) AS BEGIN OPEN prmOutCursor FOR SELECT * FROM Employee EMP WHERE EMP.EMPNO IN ( SELECT XMLTYPE.EXTRACT (VALUE (EMP),'/Value/text()').getstringval() AS iEMPNO FROM TABLE (XMLSEQUENCE (prmInClause.EXTRACT ('/ArrayList/Values/Value'))) EMP ); END; SQL Stored Procedure CREATE PROCEDURE Pass_Array_SqlServer ( @prmInClauseData XML ) AS DECLARE @XMLDocPointer INT EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @prmInClauseData BEGIN --CREATE INCLAUSE FROM INPUT PARAMETER XML VALUE SELECT * FROM EMPLOYEE WHERE EMPNO IN ( SELECT Value FROM OPENXML (@XMLDocPointer, '/ArrayList/Values', 2) WITH (Value NVARCHAR(36))) END VB.NET Code from where You will execute stored procedure 'This method will execute Oracle and SQL Server Stored Procedure and give you 'data in the form of dataset Public Sub ExecuteStoredProcedure(ByVal xmlString As String, _ ByVal connectionString As String, ByVal spName As String, ByVal IsOracle As Boolean) Dim employeeCommand As DbCommand Dim dsData As New DataSet Dim connectDB As Database = DatabaseFactory.CreateDatabase(connectionString) employeeCommand = connectDB.GetStoredProcCommand(spName) If IsOracle Then connectDB.AddXmlInParameter(employeeCommand, "@prmInClauseData", xmlString) End If connectDB.AddCursorOutParameter(employeeCommand, "@prmOutCursor") dsData = connectDB.ExecuteDataSet(employeeCommand) End Sub How to use from VB.Net code Dim xmlString As String = "<ArrayList><Values><Value>40415</Value></Values>< Values><Value>59556</Value></Values><Values><Value> 67900</Value></Values></ArrayList>" Dim sqlConnectionString As String = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\EMPLOYEE.mdf;Integrated Security=True;User Instance=True" Dim sqlSpName As String = "Pass_Array_SqlServer" 'Execute SQL Server Stored Procedure from VB.NET Code ExecuteStoredProcedure(xmlString, sqlConnectionString, sqlSpName, False) 'This will give you 3 nos of rows because XML have 3 Empno MessageBox.Show(dsDataSet.Tables(0).Rows.Count) Dim oracleConnectionString As String = "Data Source=DSNName;User ID=UserId;Password=Password;" Dim oracleSpName As String = "Pass_Array_Oracle" 'Execute Oracle Stored Procedure from VB.NET Code ExecuteStoredProcedure(xmlString, oracleConnectionString, oracleSpName, True) 'This will give you 3 nos of rows because XML have 3 Empno MessageBox.Show(dsDataSet.Tables(0).Rows.Count)