Calling a Stored Procedure with XML Datatype
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:
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.
Solution 2
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());
Solution 3
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
Solution 4
you can create a XML string using following code
var doc = new XDocument();
doc.Add(new XElement("x", input.Select(x => new XElement("v", x))));
return doc.ToString();
and then pass this doc string to stored procedure as a parameter
Solution 5
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
SqlXml sqlXml = new SqlXml(xmlReader);
cmd.Parameters.AddWithValue("@FileContent"], strxml);
Lakeshore
Updated on July 09, 2022Comments
-
Lakeshore almost 2 years
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 error: Failed to convert parameter value from a XmlDocument to a String. Below is code sample. How do you pass an XML Document to a store procedure that is expecting an XML datatype? Thanks.
XmlDocument doc = new XmlDocument(); //Load the the document with the last book node. XmlTextReader reader = new XmlTextReader(@"C:\temp\" + uploadFileName); reader.Read(); // load reader doc.Load(reader); connection.Open(); SqlCommand cmd = new SqlCommand("UploadXMLDoc", connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@Year", SqlDbType.Int); cmd.Parameters["@Year"].Value = iYear; cmd.Parameters.Add("@Quarter", SqlDbType.Int); cmd.Parameters["@Quarter"].Value = iQuarter; cmd.Parameters.Add("@CompanyID", SqlDbType.Int); cmd.Parameters["@CompanyID"].Value = iOrganizationID; cmd.Parameters.Add("@FileType", SqlDbType.VarChar); cmd.Parameters["@FileType"].Value = "Replace"; cmd.Parameters.Add("@FileContent", SqlDbType.Xml); cmd.Parameters["@FileContent"].Value = doc; cmd.Parameters.Add("@FileName", SqlDbType.VarChar); cmd.Parameters["@FileName"].Value = uploadFileName; cmd.Parameters.Add("@Description", SqlDbType.VarChar); cmd.Parameters["@Description"].Value = lblDocDesc.Text; cmd.Parameters.Add("@Success", SqlDbType.Bit); cmd.Parameters["@Success"].Value = false; cmd.Parameters.Add("@AddBy", SqlDbType.VarChar); cmd.Parameters["@AddBy"].Value = Page.User.Identity.Name; cmd.ExecuteNonQuery(); connection.Close();
-
Lakeshore over 14 yearsThanks. 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.
-
Mehmet Ergut over 14 yearsYou can take a simpler approach:
cmd.Parameters["@FileContent"].Value = new SqlXml(File.OpenRead("file.xml"));