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