Ritesh Kesharwani: May 2009

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>

Oracle Stored Procedure

CREATE OR REPLACE

PROCEDURE Pass_Array_Oracle

(

prmInClause IN XMLTYPE,

prmOutCursor OUT SYS_REFCURSOR

)

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

)

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)

Thursday, May 28, 2009

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