Calling a Stored Procedure with XML Datatype

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:

StringWriter sw = new StringWriter(); 
XmlTextWriter xw = new XmlTextWriter(sw); 
doc.WriteTo(xw); 
StringReader transactionXml = new StringReader(sw.ToString()); 
XmlTextReader xmlReader = new XmlTextReader(transactionXml); 
SqlXml sqlXml = new SqlXml(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.


To do this with an XDocument, XElement or other XNode, try the following:

XDocument doc = new XDocument(
    new XElement("Person", 
        new XAttribute("Name", "John")));
cmd.Parameters.Add("@FileContent", SqlDbType.Xml);
cmd.Parameters["@FileContent"].Value = new SqlXml(doc.CreateReader());

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

XML.DocumentElement.OuterXml 'where XML is a XMLDocument