Calling A Stored Procedure With Xml Datatype

I am simply trying to call a store procedure (SQL Server 2008) using C# and passing XMLDocument to a store procedure parameter that takes a SqlDbType.Xml data type. I am getting e

Solution 1:

You need to pass the xml as a string.

But if you don't need the xml functions in the database, you might consider using varbinary to store the files.

---

UPDATE!!!!!

Thanks. I got it to work. Added the following coded:

StringWritersw=newStringWriter(); 
XmlTextWriterxw=newXmlTextWriter(sw); 
doc.WriteTo(xw); 
StringReadertransactionXml=newStringReader(sw.ToString()); 
XmlTextReaderxmlReader=newXmlTextReader(transactionXml); 
SqlXmlsqlXml=newSqlXml(xmlReader); 

Converting it to a string was not enough. I got the following error: XML parsing: line 1, character 38, unable to switch the encoding”. So, I converted to string then coverted it to SqlXml and it worked.

Solution 2:

Other way to do it if you don't mind loosing the xml declaration (version and encoding) is just:

XML.DocumentElement.OuterXml 'whereXMLis a XMLDocument

Solution 3:

you can create a XML string using following code

var doc = newXDocument();
doc.Add(newXElement("x", input.Select(x =>newXElement("v", x))));
return doc.ToString();

and then pass this doc string to stored procedure as a parameter

Solution 4:

you can add parameter in more simpler way in this way we don't have to pass object type to parameter sql manages it as passed value

SqlXmlsqlXml=newSqlXml(xmlReader); 
cmd.Parameters.AddWithValue("@FileContent"], strxml);

Solution 5:

Another simpler way is to write the xmldoc to a string and pass that to the stored procedure.

Dim sb AsNew StringBuilder()
Dim wrtr AsNew System.IO.StringWriter(sb)
doc.Save(wrtr)
Dim strxml AsString = sb.ToString()
cmd.Parameters.Add("@FileContent", SqlDbType.Xml);
cmd.Parameters["@FileContent"].Value =strxml;