469,912 Members | 1,851 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,912 developers. It's quick & easy.

Interesting Problem with System.Data.SqlClient.SqlCommand.ExecuteXmlReader( )

A strang error is occurring when I run the following code:

SqlConnection c = new SqlConnection();
c.ConnectionString = "Initial Catalog=Northwind;user
id=sa;password=kat1ie;Data Source=server";
c.Open();
SqlCommand command = c.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "select Customers.customerid, customers.companyname, "
+
"orders.orderid, orders.orderdate " +
"from customers inner join orders " +
"on customers.customerid = orders.customerid " +
"where country = 'brazil' " +
"for xml auto, elements";

XmlReader xmlReader = command.ExecuteXmlReader();
XmlDocument doc = new XmlDocument();
doc.Load(xmlReader);

The error is as follows:

An unhandled exception of type 'System.InvalidOperationException' occurred
in system.xml.dll
Additional information: This document already has a DocumentElement node.

Why this is interesting -

Signature of System.Data.SqlClient.SqlCommand.ExecuteXmlReader( ) method:
public XmlReader ExecuteXmlReader();

Signature of System.Xml.XmlDocument.Load() method:
public virtual void Load(
XmlReader reader
);

I came across this while using the Kalani's Exam Cram 2 for 70-320 (plug for
a great series). It comes from Chapter 3, review Question 10. Their answer
is to replace the SqlCommand object with a SqlXmlCommand object. BTW
SqlXmlCommand can be found in Microsoft.Data.SqlXml namespace. For those
not familiar with this namespace, to include me up to 30 minutes ago, you
can find all the relavent information at www.msdn.com/sqlxml.

My main question here is, "why doesn't the code work?" The signatures
match. What is different about the SqlXmlCommand's implementation of the
XmlReader interface that makes it work?

Any comments or insite welcome.

Regards,
Dylan Phillips
Nov 12 '05 #1
2 4747
Dylan,

Yes, an interesting question.

It doesn't look like the XMLReader is any different, which is your point. It
appears the difference is in details of how the SQL XML is being called and
thus details of the content. Bear in mind that this is a guess.

--
Kathleen Dollard
Microsoft MVP
Author "Code Generation in Microsoft .NET"
"Dylan Phillips" <mN**********@hotmail.com> wrote in message
news:O1**************@TK2MSFTNGP09.phx.gbl...
A strang error is occurring when I run the following code:

SqlConnection c = new SqlConnection();
c.ConnectionString = "Initial Catalog=Northwind;user
id=sa;password=kat1ie;Data Source=server";
c.Open();
SqlCommand command = c.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "select Customers.customerid, customers.companyname, " +
"orders.orderid, orders.orderdate " +
"from customers inner join orders " +
"on customers.customerid = orders.customerid " +
"where country = 'brazil' " +
"for xml auto, elements";

XmlReader xmlReader = command.ExecuteXmlReader();
XmlDocument doc = new XmlDocument();
doc.Load(xmlReader);

The error is as follows:

An unhandled exception of type 'System.InvalidOperationException' occurred
in system.xml.dll
Additional information: This document already has a DocumentElement node.

Why this is interesting -

Signature of System.Data.SqlClient.SqlCommand.ExecuteXmlReader( ) method:
public XmlReader ExecuteXmlReader();

Signature of System.Xml.XmlDocument.Load() method:
public virtual void Load(
XmlReader reader
);

I came across this while using the Kalani's Exam Cram 2 for 70-320 (plug for a great series). It comes from Chapter 3, review Question 10. Their answer is to replace the SqlCommand object with a SqlXmlCommand object. BTW
SqlXmlCommand can be found in Microsoft.Data.SqlXml namespace. For those
not familiar with this namespace, to include me up to 30 minutes ago, you
can find all the relavent information at www.msdn.com/sqlxml.

My main question here is, "why doesn't the code work?" The signatures
match. What is different about the SqlXmlCommand's implementation of the
XmlReader interface that makes it work?

Any comments or insite welcome.

Regards,
Dylan Phillips

Nov 12 '05 #2
Oh to humbled by my own foolishness. Being filled with a little New Years
cheer, I realized that the issue is not the XmlReader, but rather the format
of the String being returned. The XmlDocument can only contain a single
root node, while SQL Server is returning a set of sibling nodes without a
root. Thus the nature of the detailed error message, "This document already
has a DocumentElement node."

The SqlXmlCommand must wrap the returned string from SQL Server in some root
node.
SqlDataReturned:

<row columnAttr1="value" columnAttr2="value"/>
<row .../>
<row ../>

It would work fine if SqlDataReturned

<query>
<row .../>
<row .../>
<row ../>
</query>

Oh well, it was a thought provoking question. Best wishes to all in the New
Year. Let us all pray for peace!

Dylan Phillips
"Dylan Phillips" <mN**********@hotmail.com> wrote in message
news:O1**************@TK2MSFTNGP09.phx.gbl...
A strang error is occurring when I run the following code:

SqlConnection c = new SqlConnection();
c.ConnectionString = "Initial Catalog=Northwind;user
id=sa;password=kat1ie;Data Source=server";
c.Open();
SqlCommand command = c.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "select Customers.customerid, customers.companyname, " +
"orders.orderid, orders.orderdate " +
"from customers inner join orders " +
"on customers.customerid = orders.customerid " +
"where country = 'brazil' " +
"for xml auto, elements";

XmlReader xmlReader = command.ExecuteXmlReader();
XmlDocument doc = new XmlDocument();
doc.Load(xmlReader);

The error is as follows:

An unhandled exception of type 'System.InvalidOperationException' occurred
in system.xml.dll
Additional information: This document already has a DocumentElement node.

Why this is interesting -

Signature of System.Data.SqlClient.SqlCommand.ExecuteXmlReader( ) method:
public XmlReader ExecuteXmlReader();

Signature of System.Xml.XmlDocument.Load() method:
public virtual void Load(
XmlReader reader
);

I came across this while using the Kalani's Exam Cram 2 for 70-320 (plug for a great series). It comes from Chapter 3, review Question 10. Their answer is to replace the SqlCommand object with a SqlXmlCommand object. BTW
SqlXmlCommand can be found in Microsoft.Data.SqlXml namespace. For those
not familiar with this namespace, to include me up to 30 minutes ago, you
can find all the relavent information at www.msdn.com/sqlxml.

My main question here is, "why doesn't the code work?" The signatures
match. What is different about the SqlXmlCommand's implementation of the
XmlReader interface that makes it work?

Any comments or insite welcome.

Regards,
Dylan Phillips

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Rob Wire | last post: by
14 posts views Thread by pmud | last post: by
2 posts views Thread by student | last post: by
3 posts views Thread by Adriano | last post: by
11 posts views Thread by =?Utf-8?B?R29rdWw=?= | last post: by
reply views Thread by cms3023 | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.