c# Stored Procedure / OpenXML Failure - Can Anyone Help..??

Hi,

I have some c# code which calls a SP which is erroring Basically I pass in a XML string which can be upto 5 MB is size (not sure about overflow issues here), which then calls a SP which inserts the data into a SQL table.

The c# code is as follows:

---C#-------

SqlConnection conn =new SqlConnection(DBConn);

using(StreamReader sr =new StreamReader(xmlLocationString))

{

try

{

string @.xmlInput = sr.ReadToEnd();

SqlCommand cmd =new SqlCommand();

cmd.Connection=conn;

cmd.CommandText = "[AddArgentinaTrades]";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@.xmlInput", SqlDbType.Text,5120000));

cmd.Parameters["@.xmlInput"].Direction = ParameterDirection.Output;

conn.Open();

cmd.ExecuteNonQuery();

}

catch(SqlException SqlExp)

{

Console.WriteLine(SqlExp.Message);

}

finally

{

conn.Close();

sr.Close();

}

}

------Stored Proc--------

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'AddArgentinaTrades' AND Type ='P')
DROP PROCEDURE AddArgentinaTrades
GO

CREATE PROCEDURE AddArgentinaTrades
@.xmlInput as text
AS

Declare @.idoc int

EXEC master.dbo.sp_xml_preparedocument @.idoc OUTPUT, @.xmlInput

INSERT INTO MarketRiskdev.dbo.Import_Argentina
SELECT un_cid, tnum, snum, cid, entityid, ctype, why, comp, oc, bs, ae, cp, trd_date, set_date, mat_date, val_date,
trader, famt, price, coupon, next_coupon, last_coupon, cpnfreq, cpnrate, cpntype, daycounttype, exch_notion,
contract_spot, base_cur, year_basis, buy_currency, buy_amount, sell_currency, sell_amount, [timestamp] FROM OPENXML(@.idoc, 'ArgentinaInputFile/Data',2)
WITH (un_cid varchar(50), tnum nvarchar(50), snum nvarchar(50), cid varchar(50), entityid varchar(50), ctype varchar(50),
why varchar(50),
comp varchar(50),
oc varchar(50),
bs varchar(50),
ae varchar(50),
cp varchar(50),
trd_date datetime,
set_date datetime,
mat_date datetime,
val_date datetime,
trader varchar(50),
famt float(8),
price float(8),
coupon float(8),
next_coupon datetime,
last_coupon datetime,
cpnfreq int,
cpnrate float,
cpntype int,
daycounttype smallint,
exch_notion smallint,
contract_spot float(8),
base_cur varchar(50),
year_basis int,
buy_currency varchar(50),
buy_currency varchar(50),
buy_amount float(8),
sell_currency varchar(50),
sell_amount float(8),
[timestamp] varchar(50))

EXEC master.dbo.sp_xml_removedocument @.idoc

GO

Error Msg:

A severe error occurred on the current command. The results, if any, should be
discarded.

Can anyone help here as I have no idea. I have tried reducing the size of XML to 5KB and still get the same error??

I don't think it is the size of your xml file, try the link below to modify your SQL statement. Hope this helps.

http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx