SqlCommand.Exec uteXmlReader

[Soren Jorgensen]

Hi,

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

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

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

string result = "";

SqlCommand comm = conn.CreateComm and();
comm.CommandTyp e = CommandType.Tex t;
comm.CommandTex t = sql;

XmlReader reader = comm.ExecuteXml Reader();
reader.MoveToCo ntent();
result = reader.ReadOute rXml();
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...316016&SD=MSDN

Hope this helps,
--- Nick

"Soren Jorgensen" <[e-mail]> wrote in message
news:u5******** ******@TK2MSFTN GP10.phx.gbl...

Hi,

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

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

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

string result = "";

SqlCommand comm = conn.CreateComm and();
comm.CommandTyp e = CommandType.Tex t;
comm.CommandTex t = sql;

XmlReader reader = comm.ExecuteXml Reader();
reader.MoveToCo ntent();
result = reader.ReadOute rXml();
reader.Close();

comm.Dispose();

Why the difference ??

Thanks in advance

Soren