(Beginner) Retrieve XML data into a Dataset

[John Francisco Williams]

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.

[John Francisco Williams]

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.

[Bryant Likes]

"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

[John Francisco Williams]

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