Is there a better way to read one xml value from the database

Question

I'm currently using this code fragment to retrieve the one xml value that will be returned from my scalar query.

using (SqlDataReader reader = sqlCommand.ExecuteReader())
{
    while (reader.Read())
    {
        SqlXml xml = reader.GetSqlXml(0);
        XmlSchema xmlSchema = XmlSchema.Read(xml.CreateReader(), validationEvent);
        break;
    }
}

I would prefer to use sqlCommand.ExecuteScalar() but it seems that to do that means bringing back to the xml into a String which seems wrongheaded to me, since I'm not interested in having a String.

Answer 1

SqlCommand.ExecuteXmlReader

Answer 2

Actually, by running ExecuteNonQuery, you can only return an int (the return value of the stored procedure). Except for ExecuteScalar, you have 2 options:

1. ExecuteXMLReader

2. Use an output parameter (I would prefer to use Scalar over this)

Answer 3

From the MSDN documentation on SqlDbType.Xml

An XML value. Obtain the XML as a string using the GetValue method or Value property, or as an XmlReader by calling the CreateReader method.

So you will either end up with your existing code, or get the xml returned as a string with ExecuteScalar, which you must parse into an xml object.