Error while inserting XML data through stored procedure

Question

I have a table with the column's datatype as xml in SQL Server 2005. I created a stored procedure to insert values into that column.

But when calling the SP from code in C#, its giving me an error

Failed to convert parameter value from a XDocument to a String.

Can anyone help with this?

This is the stored procedure I created:

Create Procedure [dbo].[TestReportRepository_Save]
@ReportExecutionTime datetime,
@ReportFile xml,

as
begin

insert into TestReportRepository(ReportExecutionTime,ReportFile) values(@ReportExecutionTime,@ReportFile)

end

The C# code is

DbParameter dbParam1 = dac.Parameter("@ReportExecutionTime", ReportExecutionTime, DbType.DateTime, ParameterDirection.Input);
DbParameter dbParam2 = dac.Parameter("@ReportFile", xmlDoc.Document, DbType.Xml, ParameterDirection.Input);

DbParameter[] dbParamColl = new DbParameter[] { dbParam1, dbParam2 };
long reportID = dac.Save("TestReportRepository_save", dbParamColl);

Please help me identify what I am doing wrong.

If you post code, XML or data samples, PLEASE highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! — marc_s
– marc_s, Commented Feb 2, 2012 at 11:27
Where in your C# code are you supplying the values for the parameters?? I don't see that. Basically - the XML type of SQL Server is a glorified string - it cannot deal with an XDocument directly - you'll have to "serialize" that XDocument into a string to pass it to SQL Server (e.g. usign the .ToString() method) — marc_s
– marc_s, Commented Feb 2, 2012 at 11:33
Thanks @marc_s: I will keep your suggestion in mind while posting any new Question. Also Thanks for the solution it worked out. — Sumit Gupta
– Sumit Gupta, Commented Feb 2, 2012 at 13:22

Oleg Dok · Accepted Answer · 2012-02-02 11:40:29Z

3

When passing this parameter - pass not XDocument object, but its string representation.

For XmlDocument it is XmlDocument.OuterXml, find one for XDocument and pass it

answered Feb 2, 2012 at 11:40

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Oleg Dok Over a year ago

@mark_s Sorry, I've not noticed your comment when wrote the answer, if you decide that it is a rep whoring - i'll delete the answer

marc_s Over a year ago

No worries :-) I just didn't feel like expanding this to a "real" answer - you did - you deserve the credit for your effort!

Sumit Gupta Over a year ago

@OlegDok Useful information from your side. Thanks for the help.

Collectives™ on Stack Overflow

Error while inserting XML data through stored procedure

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related