How to return XML data from Store procedure ???

[Guest]

Dear all,

From an ASP.net application I am calling a store procedure from an SQL
server 2000 database, defined as follow :

"SELECT * FROM TABLE1 FOR XML AUTO"

How to retrieve the result of the store procedure back to my code and format
it properly ??

thnaks for your help
regards

 

[Cor Ligthert [MVP]]

Serge,

As I see it in the documentation than it returns an XML document (not a
dataset).

In my opinion is therefore the XML nodereader the nicest to process.
http://www.vb-tips.com/default.aspx?ID=e788c048-e547-4de3-9c6a-22589f018cd4


I never tried it however because that only one value is returned, would I
try first to get the data using the executescalar.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(VS.80).aspx

Otherwise you would have to use the XMLreader which is described for this.

http://msdn2.microsoft.com/en-us/li...lient.sqlcommand.executexmlreader(VS.80).aspx

I hope this helps,

Cor

 

[Steve Fortner]

I've been struggling with using the sql extensions like for xml and
openxml myself, but it seems that Microsoft is really pushing DataSets
now with the addition of ADO.NET, instead of using XML or DataReaders.
Check out
http://msdn.microsoft.com/library/default.asp?url=/msdnmag/issues/02/11/datapoints/TOC.asp
and
http://www.knowdotnet.com/articles/datarelation.html to start, then
maybe http://msdn.microsoft.com/msdnmag/issues/04/05/DataPoints/ as
well. If you just write your query as "select * from table1" you can
use a DataAdapter to read it in, fill a DataSet, then use the DataSet's
GetXml() method if you want to transform it to xml. Or use the
DataSet's ReadXml() method to read in an xml string or xml document
from file. Or the WriteXml() method to write the DataSet in XML form
to a file. You can even read in an xml xsd schema with the DataSet's
ReadXmlSchema() method. It almost seems like Microsoft has taken a
step back by banking on DataSet's, but they've really got some pretty
good arguments for using them. One thing they also give you that XML
doesn't is the ability to establish constraints with the DataTable and
DataRelations, instead of using a schema. It's more integrated, and
provides a better way of inserting/updating/deleting records in a
database. Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql25xmlbp.asp
to see this in practice, by using UpdateGrams to manage data change.
Plus, the DataSets don't duplicate data like a join on several tables
will produce. It's a hierarchical vs. relational model. This may be
more for a disconnected database model, or for use on the Web, for
better speed, but I'm almost seeing at this point to get away from the
old way of doing things with DataReaders, event though they're fast.
They have their place, if you're only needing fast, read-only access to
the data though. It almost seems like DataSets are replacing the old
XML/DOM model, but I'm really just starting to get a handle on the
DataSets. Good luck,

--Steve

 

[Guest]

HI, thnaks for your answers.

So far I am always using dataset on datareader or execute scalar depending
on the amount of data I have to retrive form my database. Then I drop by
mistake to that FOR XML instruction in SQL server so I was wondering how this
is used ( I have not so much experience on SQL server side) and especially
data return when this instruction is used.

I was imagine for instance that you need to provide such data to an
aplication which is not supporting dataset, so in my case you would nee to
retrive those return data inside an XMLDocument. Passing through a dataset
first and then generate the XML Document would be the easiest and fast way
for sure.

I have heard and read many things on dataset as well as there are a lot
memory consumer based on amount of data collected. But as I could see we
cannot escape from that and everybody pass to that type of object.

Regards
Serge
MCAD.NET