Reading XML from Database - Serious Problem - Need Help

[Christopher Ambler]

This is long, but it's driving me nuts. I need some adult supervision :-)
(and I'm not above bribing for help)

I have a stored procedure that I call that returns XML to me. The SP returns
3 recordsets. The first comes as a single XML tag like this:

<row recordfound="1" IsExpired="0" />

The second has any number of recordsets that look like this:

<contact>
<contacttype>Individual</contacttype>
<fname>Fred</fname>
<lname>Flintstone</lname>
</contact>

And the third has any number of recordsets that look like this:

<nameservers>
<ns>ns1.example.com</ns>
</nameserver>

The ultimate goal here is to understand what the XML looks like coming out
of the SP, so I can deserialize it into the appropriate classes before I
play with it. Deserialization, I think I understand somewhat. But it's
failing - so I tried to consume this information in a number of different
ways, and each way shows me DIFFERENT STUFF. I'm baffled. Here are my
different ways, and what they produce:

The first way I tried was to use ExecuteReader() on my command, and then
build the output using a loop, as such:

StringBuilder sb = new StringBuilder();
do
{
while (r.Read())
{
for (int i = 0; i < r.FieldCount; i++)
sb.Append(r.GetString(i));
}
} while (r.NextResult());

return sb.ToString();

This returns me all of the XML tags that I expect, as I outlined, above.
First the <row> element, then a bunch of <contact> elements, and then some
<nameserver> elements. It looks just like I'd expect. So why isn't
deserialization working? I decided to look at it in a more "raw" way. Then
it got weird. I used a DataSet and spat out the XML that the DataSet had.
For some reason, all of the angle-bracket tags are, in the string, replaced
with their HTML-encoding equivelents. I have no idea why. The most confusing
part of this is that the output has extra tags that I don't understand! The
output actually looks like this:

<NewDataSet>
<Table>
<XML_F7384B61-2837-1243-B105-99384F8438B><row recordfound="1"
IsExpired="0" /></XML_F7384B61-2837-1243-B105-99384F8438B>
</Table>
<Table1>
<XML_F7384B61-2837-1243-B105-99384F8438B>
<contact>
<contacttype>Individual</contacttype>
<fname>Fred</fname>
<lname>Flintstone</lname>
</contact>
</XML_F7384B61-2837-1243-B105-99384F8438B>
</Table1>
<Table1>
<XML_F7384B61-2837-1243-B105-99384F8438B>
<contact>
<contacttype>Individual</contacttype>
<fname>Wilma</fname>
<lname>Flintstone</lname>
</contact>
</XML_F7384B61-2837-1243-B105-99384F8438B>
</Table1>

.... and so on ...
</NewDataSet>

I have no idea what NewDataSet is, or the XML_[guid] tag is, though I must
presume that the SQL server is inserting them. Could this be why
deserialization is failing?

Here's the code that generates this output:

public string DataSetMethod(string domain)
{
this.sqlConnection.Open();
this.mySQLCommand.Parameters["@strDomainName"].Value = domain;
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(this.sqlWHOIS);
da.Fill(ds);
StringWriter sr = new StringWriter();
ds.WriteXml(sr);
this.sqlConnection.Close();
string result = sr.ToString();

// Note that I have to do this replace, because the < and > are replaced
with &lt; and &gt; ... confusing.

return result.Replace("&lt;", "<").Replace("&gt;", ">");
}

The reason I'm so confused here is, as I said, that I want to deserialize
all of this. I've created the appropriate classes, but it constantly fails,
with CanDeserialize() always returning false. The curious thing is, before
we added the first recordset (with the single tag that has attributes but no
data), it worked just fine. Is this a clue?

Knowing what my "contact" tag looks like, I should be able to deserialize
into this:

[Serializable]
public class Contact
{
public string ContactType;
public string FName;
public string LName;
}

And I should be able to do this:

XmlSerializer ser = new XmlSerializer(typeof(Contact));
XmlTextReader r = (XmlTextReader)this.mySQLCommand.ExecuteXmlReader( );

if (ser.CanDeserialize(r))
{
Contact c = (Contact)ser.Deserialize(r));
}
else
{
ReportError("Cannot deserialize Contact");
}

But no. It fails, and reports the "Cannot deserialize Contact" error.

So... to summarize my confusion:

What's the extra junk that SQL server is sending down?
Why, when I get the XML out of the DataSet are all of the angle brackets
replaced with their HTML-encoded equivelents?
And why is CanDeserialize() always telling me that it doesn't like my
contact class, when it looks correct?!

If I could just get this working, I think I understand how I make a root
class that holds a collection of Contact and Nameserver classes, to
deserialize all of the XML into, so I can pass an instance of the class back
out to my program to play with.

HELP?!

Christopher