What does the SQL Server XML datatype translate to in .NET and how do I convert it to XmlDocument?

Question

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.

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.

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?

Answer 1

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.

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

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.

Answer 2

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

Answer 3

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

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