By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,534 Members | 1,807 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,534 IT Pros & Developers. It's quick & easy.

(Beginner) Retrieve XML data into a Dataset

P: n/a
Hi, I searched through this newsgroup looking for an answer for this, but
didn't find any (it probably is here. Just didn't find it).

I am trying to retrieve information from two related tables, using For XML
Auto, and then use that in my C# app. I am only receiving the
first row (and its child rows). This is what I do:

Stored Procedure:
Create Procedure dbo.uspTest04
As
Select
Top 5
Oe.OrderID,
CustomerID,
EmployeeID,
ProductID ,
UnitPrice ,
Quantity ,
Discount
From dbo.Orders As Oe( NoLock )
Inner Join dbo.[Order Details] As Od( NoLock )
On Oe.OrderID = Od.OrderID
For XML Auto
Return

C# code:
SqlCommand cmmExecuteProcedure;
SqlConnection cnnTheConnection ;
DataSet dtsTablas ;

cnnTheConnection = new SqlConnection( strConnectionString );

cmmExecuteProcedure = new SqlCommand( "dbo.uspTest04", cnnTheConnection );
cmmExecuteProcedure.CommandType = CommandType.StoredProcedure;

cnnTheConnection.Open();

dtsTablas = new DataSet();
dtsTablas.ReadXml( cmmExecuteProcedure.ExecuteXmlReader() );

cnnTheConnection.Close();

dtgResultados.DataSource = dtsTablas;

If I use For XML Auto, XMLData in the stored procedure and specify the read
mode for the DataSet as 'fragment', it works. But SQL Server help suggests
not to use "XMLData":

Procedure:
Create Procedure dbo.uspTest05
As
Select
Top 5
Oe.OrderID,
CustomerID,
EmployeeID,
ProductID ,
UnitPrice ,
Quantity ,
Discount
From dbo.Orders As Oe( NoLock )
Inner Join dbo.[Order Details] As Od( NoLock )
On Oe.OrderID = Od.OrderID
For XML Auto, XMLData
Return

C# code:
SqlCommand cmmExecuteProcedure;
SqlConnection cnnTheConnection ;
DataSet dtsTablas ;

cnnTheConnection = new SqlConnection( strConnectionString );

cmmExecuteProcedure = new SqlCommand( "dbo.uspTest05", cnnTheConnection );
cmmExecuteProcedure.CommandType = CommandType.StoredProcedure;

cnnTheConnection.Open();

dtsTablas = new DataSet();
dtsTablas.ReadXml( cmmExecuteProcedure.ExecuteXmlReader(),
XmlReadMode.Fragment );

cnnTheConnection.Close();

dtgResultados.DataSource = dtsTablas;
What do I need to do, to receive all the information in the DataSet, without
using "For XML Auto, XMLData"? Also, I see that the XML result comes without
a

root. How do I make it come with a root, so that I can use, for example:

ThisXMLDocument.Load( cmmExecuteProcedure.ExecuteXmlReader() )

?

Thank you very much for the help. Have a great day,

Frank.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
I changed the client code to fill the Dataset, like this:

dtsResults = new DataSet();
while ( !xrdResults.EOF )
{
dtsResults.ReadXml( xrdResults );
}

And now, it looks like it works ok. I'm able to see all rows (and their
child rows). The stored procedure is using "For XML Auto" and not "For XML
Auto, XMLData". Is all this the correct way to do it, or should I use
xrdResults.MoveToContent() to avoid any possible error?

Thanks in advance,

Frank
"John Francisco Williams" <Cu******@e-ciety.com> wrote in message
news:eC**************@TK2MSFTNGP12.phx.gbl...
Hi, I searched through this newsgroup looking for an answer for this, but
didn't find any (it probably is here. Just didn't find it).

I am trying to retrieve information from two related tables, using For XML
Auto, and then use that in my C# app. I am only receiving the
first row (and its child rows). This is what I do:

Stored Procedure:
Create Procedure dbo.uspTest04
As
Select
Top 5
Oe.OrderID,
CustomerID,
EmployeeID,
ProductID ,
UnitPrice ,
Quantity ,
Discount
From dbo.Orders As Oe( NoLock )
Inner Join dbo.[Order Details] As Od( NoLock )
On Oe.OrderID = Od.OrderID
For XML Auto
Return

C# code:
SqlCommand cmmExecuteProcedure;
SqlConnection cnnTheConnection ;
DataSet dtsTablas ;

cnnTheConnection = new SqlConnection( strConnectionString );

cmmExecuteProcedure = new SqlCommand( "dbo.uspTest04", cnnTheConnection );
cmmExecuteProcedure.CommandType = CommandType.StoredProcedure;

cnnTheConnection.Open();

dtsTablas = new DataSet();
dtsTablas.ReadXml( cmmExecuteProcedure.ExecuteXmlReader() );

cnnTheConnection.Close();

dtgResultados.DataSource = dtsTablas;

If I use For XML Auto, XMLData in the stored procedure and specify the read mode for the DataSet as 'fragment', it works. But SQL Server help suggests
not to use "XMLData":

Procedure:
Create Procedure dbo.uspTest05
As
Select
Top 5
Oe.OrderID,
CustomerID,
EmployeeID,
ProductID ,
UnitPrice ,
Quantity ,
Discount
From dbo.Orders As Oe( NoLock )
Inner Join dbo.[Order Details] As Od( NoLock )
On Oe.OrderID = Od.OrderID
For XML Auto, XMLData
Return

C# code:
SqlCommand cmmExecuteProcedure;
SqlConnection cnnTheConnection ;
DataSet dtsTablas ;

cnnTheConnection = new SqlConnection( strConnectionString );

cmmExecuteProcedure = new SqlCommand( "dbo.uspTest05", cnnTheConnection );
cmmExecuteProcedure.CommandType = CommandType.StoredProcedure;

cnnTheConnection.Open();

dtsTablas = new DataSet();
dtsTablas.ReadXml( cmmExecuteProcedure.ExecuteXmlReader(),
XmlReadMode.Fragment );

cnnTheConnection.Close();

dtgResultados.DataSource = dtsTablas;
What do I need to do, to receive all the information in the DataSet, without using "For XML Auto, XMLData"? Also, I see that the XML result comes without a

root. How do I make it come with a root, so that I can use, for example:

ThisXMLDocument.Load( cmmExecuteProcedure.ExecuteXmlReader() )

?

Thank you very much for the help. Have a great day,

Frank.

Nov 12 '05 #2

P: n/a
"John Francisco Williams" <Cu******@e-ciety.com> wrote in message
news:eC**************@TK2MSFTNGP12.phx.gbl...
[snip]
I am trying to retrieve information from two related tables, using For XML
Auto, and then use that in my C# app. I am only receiving the
first row (and its child rows). This is what I do:


Take a look at the SQLXML Managed Classes:
http://msdn.microsoft.com/library/en...otnet_775e.asp

--
Bryant
Nov 12 '05 #3

P: n/a
Excelent :-) Thank you very much

Frank

"Bryant Likes" <br****@suespammers.org> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
"John Francisco Williams" <Cu******@e-ciety.com> wrote in message
news:eC**************@TK2MSFTNGP12.phx.gbl...
[snip]
I am trying to retrieve information from two related tables, using For XML Auto, and then use that in my C# app. I am only receiving the
first row (and its child rows). This is what I do:


Take a look at the SQLXML Managed Classes:
http://msdn.microsoft.com/library/en...otnet_775e.asp

--
Bryant

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.