(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 cmmExecuteProce dure;
SqlConnection cnnTheConnectio n ;
DataSet dtsTablas ;

cnnTheConnectio n = new SqlConnection( strConnectionSt ring );

cmmExecuteProce dure = new SqlCommand( "dbo.uspTest04" , cnnTheConnectio n );
cmmExecuteProce dure.CommandTyp e = CommandType.Sto redProcedure;

cnnTheConnectio n.Open();

dtsTablas = new DataSet();
dtsTablas.ReadX ml( cmmExecuteProce dure.ExecuteXml Reader() );

cnnTheConnectio n.Close();

dtgResultados.D ataSource = 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 cmmExecuteProce dure;
SqlConnection cnnTheConnectio n ;
DataSet dtsTablas ;

cnnTheConnectio n = new SqlConnection( strConnectionSt ring );

cmmExecuteProce dure = new SqlCommand( "dbo.uspTest05" , cnnTheConnectio n );
cmmExecuteProce dure.CommandTyp e = CommandType.Sto redProcedure;

cnnTheConnectio n.Open();

dtsTablas = new DataSet();
dtsTablas.ReadX ml( cmmExecuteProce dure.ExecuteXml Reader(),
XmlReadMode.Fra gment );

cnnTheConnectio n.Close();

dtgResultados.D ataSource = 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( cmmExecuteProce dure.ExecuteXml Reader() )

?

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.Read Xml( 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.Move ToContent() to avoid any possible error?

Thanks in advance,

Frank
"John Francisco Williams" <Cu******@e-ciety.com> wrote in message
news:eC******** ******@TK2MSFTN GP12.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 cmmExecuteProce dure;
SqlConnection cnnTheConnectio n ;
DataSet dtsTablas ;

cnnTheConnectio n = new SqlConnection( strConnectionSt ring );

cmmExecuteProce dure = new SqlCommand( "dbo.uspTest04" , cnnTheConnectio n );
cmmExecuteProce dure.CommandTyp e = CommandType.Sto redProcedure;

cnnTheConnectio n.Open();

dtsTablas = new DataSet();
dtsTablas.ReadX ml( cmmExecuteProce dure.ExecuteXml Reader() );

cnnTheConnectio n.Close();

dtgResultados.D ataSource = 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 cmmExecuteProce dure;
SqlConnection cnnTheConnectio n ;
DataSet dtsTablas ;

cnnTheConnectio n = new SqlConnection( strConnectionSt ring );

cmmExecuteProce dure = new SqlCommand( "dbo.uspTest05" , cnnTheConnectio n );
cmmExecuteProce dure.CommandTyp e = CommandType.Sto redProcedure;

cnnTheConnectio n.Open();

dtsTablas = new DataSet();
dtsTablas.ReadX ml( cmmExecuteProce dure.ExecuteXml Reader(),
XmlReadMode.Fra gment );

cnnTheConnectio n.Close();

dtgResultados.D ataSource = 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( cmmExecuteProce dure.ExecuteXml Reader() )

?

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******** ******@TK2MSFTN GP12.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****@suespam mers.org> wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..

"John Francisco Williams" <Cu******@e-ciety.com> wrote in message
news:eC******** ******@TK2MSFTN GP12.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