sproc using 'for xml' and then ExecuteXmlReader in C# -> "Data is Null. This method or property cannot be called on Null values."

I have some code:

XmlDocument doc = new XmlDocument();

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString()))
            {
                SqlCommand command = connection.CreateCommand();

                command.CommandText = "sproc1";
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(
                        new SqlParameter("@Param1",2));

        开发者_C百科        connection.Open();
                doc.Load(command.ExecuteXmlReader());
            }

which loads an xml document from a sproc. I get:

Data is Null. This method or property cannot be called on Null values.

when the sproc returns null as it uses:

for xml raw, type, root('rows')

to transform the table results into xml.

How can I avoid this? Either in my sql code or deal with it in C#?

Thanks.

Chris

The best work around I have found so far is to use this in my sql code:

DECLARE @XML XML;
...
SELECT @XML = (select
...
for xml raw, type, root('rows'))
...
select case when @XML is not null then @XML else '<rows></rows>' end

OK.. what about this code?

connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable table = new DataTable();
adapter.Fill(table);
if (table.Rows.Count > 0)
{
    using (MemoryStream stream = new MemoryStream())
    {
        XmlWriter writer = XmlTextWriter.Create(stream);
        table.WriteXml(writer);
        XmlReader reader = XmlReader.Create(stream);
        doc.Load(reader);
    }
}