SqlCommand.ExecuteXmlReader

[Soren Jorgensen]

Hi,

When executing following statement "select distinct Name from
CustomerParameters for xml auto"

- XmlReader reads "<customerparameters name="Www.Mail.Generel" />"
- but SQL Query Manager returns "<CustomerParameters
Name="Www.Mail.Generel"/><CustomerParameters Name="Www.Mail.Updates"/>"

I'm using following to read the result returned from the SqlConnection

string result = "";

SqlCommand comm = conn.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = sql;

XmlReader reader = comm.ExecuteXmlReader();
reader.MoveToContent();
result = reader.ReadOuterXml();
reader.Close();

comm.Dispose();

Why the difference ??

Thanks in advance

Soren

 

[Nick Malik]

Hello Soren,

You have forgotten a loop.

Normally, XML Reader expects to read a single XML document. A single XML
document will have a parent node and one or more child nodes:
<parent>
<child/>
<child/>
</parent>

However, SQL Server doesn't return an XML document. It just returns the
child nodes:
<child/>
<child/>

As such, the XML Reader finds the first node, assumes that it is the entire
document, and returns it.

You need to put XML Reader in a loop to get all the records.

See an example at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q316016&ID=kb;en-us;Q316016&SD=MSDN

Hope this helps,
--- Nick