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

Calling a Stored Procedure with XML Datatype

提问by Lakeshore

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 转换为字符串。下面是代码示例。如何将 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();

采纳答案by Mehmet Ergut

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!!!!!

更新！！！！！

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 并且它起作用了。

回答by Faraz

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;

回答by alcobiavieira

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

回答by Haider Ali Wajihi

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

回答by John Saunders

To do this with an XDocument, XElementor 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());

回答by Michael Erickson

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

回答by Moulidharan Kandregula

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

回答by Peter

Or, with the fewest lines of code, read your XmlDocument straight into an XmlNodeReader and use that to initialise you SqlXml parameter value:

或者，使用最少的代码行，将您的 XmlDocument 直接读入 XmlNodeReader 并使用它来初始化您的 SqlXml 参数值：

SqlXml sqlXml= new SqlXml(new XmlNodeReader(doc));
cmd.Parameters.Add("@FileContent", sqlXml);

Note that you don't need to add the parameter with the type, then set the value - if you pass a type which SqlParameter recognises (in this case a SqlXml object), the type will be inferred.

请注意，您不需要使用类型添加参数，然后设置值 - 如果您传递 SqlParameter 识别的类型（在本例中为 SqlXml 对象），则将推断该类型。

回答by Sonal

you can create a XML string using following code

您可以使用以下代码创建 XML 字符串

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

然后将此文档字符串作为参数传递给存储过程