Search code examples
c#.netsqlxml-serializationsqlparameter

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

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

Solution

  • 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.