SQL Server XML数据类型在.NET中转换为什么？如何将其转换为XmlDocument？

We have a column in the database that has a type of xml. I am reading this information via the .net SqlDataReader, but I'm not sure what to cast it to.

我们在数据库中有一个类型为xml的列。我正在通过.net SqlDataReader读取此信息,但我不确定要将其转换为什么。

The msdn table (http://msdn.microsoft.com/en-us/library/cc716729.aspx) suggests that is of the .net type Xml, but there is no System.Xml, only System.Web.UI.WebControls.Xml so I'm not sure if that is correct.

msdn表(http://msdn.microsoft.com/en-us/library/cc716729.aspx)表明它是.net类型的Xml,但是没有System.Xml,只有System.Web.UI.WebControls .Xml所以我不确定这是否正确。

So my question is this:

所以我的问题是:

What do I cast the SqlDbType.Xml to as I'm reading it from a SqlDataReader, and how do I convert that to XmlDocument?

当我从SqlDataReader读取它时,我将SqlDbType.Xml转换为什么,以及如何将其转换为XmlDocument?

4 个解决方案

#1

I remember casting it to a string. Feeding XmlDocument with a string works as usual then.

我记得把它扔成一个字符串。用字符串提供XmlDocument就像平常一样。

#2

It translates to SqlXml and you can get an XmlReader with SqlXml.CreateReaderfrom it. You'd have to use SqlDataReader.GetSqlXmlmethod to get the type instead of a string.

它转换为SqlXml,你可以从它获得一个带有SqlXml.CreateReader的XmlReader。您必须使用SqlDataReader.GetSqlXml方法来获取类型而不是字符串。

For example:

        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            SqlXml xmlData =
            reader.GetSqlXml(0);
            XmlReader xmlReader = xmlData.CreateReader();

            xmlReader.MoveToContent();
            while (xmlReader.Read())
            {
                if (xmlReader.NodeType == XmlNodeType.Element)
                {
                    string elementName = xmlReader.LocalName;
                    xmlReader.Read();
                    Console.WriteLine(elementName + ": " + xmlReader.Value);
                }
            }
        }

UPDATE: To answer a helpful comment from @Wiktor Zychla

更新:回答@Wiktor Zychla的有用评论

The performance of this approach is better and can be a lot better when dealing with large XML fields because SqlReader.GetString will load the field content into a string first while SqlReader.GetSqlXml creates an XmlReader from the stream directly. That can be quickly verified with a look at System.Data in Reflector or a similar tool.

这种方法的性能更好,并且在处理大型XML字段时可以更好,因为SqlReader.GetString将首先将字段内容加载到字符串中,而SqlReader.GetSqlXml直接从流中创建XmlReader。可以通过查看Reflector中的System.Data或类似工具来快速验证。

#3

I use this method myself, using SqlCommand.ExecuteXmlReader();

我自己使用这个方法,使用SqlCommand.ExecuteXmlReader();

XmlDocument xdoc = new XmlDocument();
using (SqlCommand command = new SqlCommand(queryString, connection))
{
    XmlReader reader = command.ExecuteXmlReader();
    if (reader.Read())
    {
        xdoc.Load(reader);
    }
}

#4

Sure, there's a System.Xml namespace:

当然,有一个System.Xml命名空间:

The System.Xml namespace provides standards-based support for processing XML.

System.Xml命名空间为处理XML提供基于标准的支持。

To use it, though, you'll probably have to add it as a reference in your project. Microsoft has instructions for doing this in Visual Studio.

但是,要使用它,您可能必须将其添加为项目中的参考。 Microsoft有在Visual Studio中执行此操作的说明。

#1