Hi Guys,

I'm calling a stored procedure from my VB.NET application and it passes the following SQLParameters.

sdiParameters(0) = New SqlParameter("@RequestXML", RequestXML.InnerXml)
sdiParameters(0).SqlDbType = SqlDbType.Xml
sdiParameters(0).Size = RequestXML.InnerXml.Length
sdiParameters(1) = New SqlParameter("@UserID", UserID)
sdiParameters(1).SqlDbType = SqlDbType.Int
sdiParameters(2) = New SqlParameter("@FailureMessage", System.DBNull.Value)
sdiParameters(2).Direction = ParameterDirection.InputOutput
sdiParameters(2).SqlDbType = SqlDbType.NVarChar
sdiParameters(2).Size = 255
sdiParameters(3) = New SqlParameter("@SessionID", System.DBNull.Value)
sdiParameters(3).Direction = ParameterDirection.InputOutput
sdiParameters(3).SqlDbType = SqlDbType.Int
sdiParameters(4) = New SqlParameter("@ProcessXML", System.DBNull.Value)
sdiParameters(4).Direction = ParameterDirection.InputOutput
sdiParameters(4).SqlDbType = SqlDbType.Xml
sdiParameters(4).Size = 1 ' * * * Why do I need to specify this? * * *
sdiParameters(5) = New SqlParameter("@RETURN_VALUE", System.DBNull.Value)
sdiParameters(5).Direction = ParameterDirection.ReturnValue

sdiParameters(4) is an Output parameter and the XML will be loaded into a XMLDocument after the SP executes.

I get the following error "System.InvalidOperationException: String[4]: the Size property has an invalid size of 0." if I do not specify the length of sdiParameters(4). As it is returned from the SP I do not know what the length will be before the SP executes.

If I set the size to 1, it works and executes successfully.

Any ideas why?

Cheers,

Francis

It gave me good idea on XML as parameters.

How to set the value to the parameter which is SqlDbType.XML type.

I need to send an XML file as parameter to SQL Stored Procedure and save the data of XML file in the SQL Server Database.

Can you please provide me with the sample code of assigning XML file to the Stored Procedure and in the Stored Procedure of how to navigate thru XML file and save in the table of SQL Server database.

Thanks in Advance
prabhakar