Is it possible to create an XmlReader from output SqlParameter of type SqlDbType.Xml?

This is my parameter definition:

var param = new SqlParameter
{
    ParameterName = "@param",
    SqlDbType = SqlDbType.Xml,
    Direction = ParameterDirection.Output,
    Size = int.MaxValue
};
command.Parameters.Add(param);

Then I do:

command.ExecuteNonQuery();

And finally:

XmlSerializer serializer = new XmlSerializer(typeof(MyClass));
return serialize开发者_开发知识库r.Deserialize(
    new MemoryStream(Encoding.UTF8.GetBytes(param.Value.ToString())))
    as MyClass;

Do I really need to convert to string and then byte array?

Use Parameter.SqlValue, will return a SqlXml instance and you can use CreateReader to get an XML reader. Then use the XmlSerializer.Deserialize(XmlReader) overwrite.

If the XML is large, you should consider using CommandBehavior.SequentialAccess.

You can also do in this way:

          cnn = new SqlConnection();
          cnn.ConnectionString = "xxxxxxxxxxxxxxxxx";
          cnn.Open();

          string selectQry = "SELECT [Xml] FROM [Table1] WHERE [PK_ID] = @ID";
          cmd = new SqlCommand(selectQry, cnn);
          cmd.Parameters.AddWithValue("@ID", ID);

          XmlReader reader = cmd.ExecuteXmlReader();

          if (reader.Read())
             xdoc.Load(reader);