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

  • Thread starter Thread starter Dylan Phillips
  • Start date Start date
D

Dylan Phillips

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
 
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
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
 
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
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
 
Back
Top