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

Question

This is the definition of my parameter:

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 serializer.Deserialize(
    new MemoryStream(Encoding.UTF8.GetBytes(param.Value.ToString())))
    as MyClass;

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

Answer 1

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

If the XML is large, you should use CommandBehavior.SequentialAccess.

Answer 2

You can also do this:

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