使用XML数据类型调用存储过程

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.

我只是尝试使用C＃调用存储过程（SQL Server 2008）并将XMLDocument传递给采用SqlDbType.Xml数据类型的存储过程参数。我收到错误：无法将参数值从XmlDocument转换为String。下面是代码示例。如何将XML文档传递给期望XML数据类型的存储过程？谢谢。

        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();

7 个解决方案

#1

You need to pass the xml as a string.

您需要将xml作为字符串传递。

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

但是，如果您不需要数据库中的xml函数，则可以考虑使用varbinary来存储文件。

UPDATE!!!!!

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.

将它转换为字符串是不够的。我收到以下错误：XML解析：第1行，第38个字符，无法切换编码“。所以，我转换为字符串，然后将它转换为SqlXml，它的工作原理。

#2

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

如果您不介意丢失xml声明（版本和编码），其他方法就是：

XML.DocumentElement.OuterXml 'where XML is a XMLDocument

#3

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

要使用XDocument，XElement或其他XNode执行此操作，请尝试以下操作：

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());

#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

你可以用更简单的方式添加参数，这样我们就不必将对象类型传递给参数sql将其作为传递值进行管理

SqlXml sqlXml = new SqlXml(xmlReader); 
cmd.Parameters.AddWithValue("@FileContent"], strxml);

#5

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

另一种更简单的方法是将xmldoc写入字符串并将其传递给存储过程。

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

#6

In .NET Framework 4.5.2, I was able to pass a System.Xml.XmlDocument (variable name "xdoc") object using the following simple code:

在.NET Framework 4.5.2中，我能够使用以下简单代码传递System.Xml.XmlDocument（变量名称“xdoc”）对象：

XmlTextReader xreader = new XmlTextReader(new StringReader(xdoc.OuterXml));
cmd.Parameters.Add(new SqlParameter("@xmlOptions", new SqlXml(xreader)));

#7

public static string SerializeToXml(T obj)
{
    XmlSerializerNamespaces ns = new XmlSerializerNamespaces();

    ns.Add("","");

    StringWriter Output = new StringWriter(new StringBuilder());
    XmlSerializer ser = new XmlSerializer(obj.GetType);
    ser.Serialize(Output, obj, ns);

    return Output.ToString();
}

#1