|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to return XML data from Store procedure ???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 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/library/system.data.sqlclient.sqlcommand.executexmlreader(VS.80).aspx I hope this helps, Cor Show quote "serge calderara" <sergecalder***@discussions.microsoft.com> schreef in bericht news:BD2A28A1-3B39-4E38-B298-E85E4E737C82@microsoft.com... > 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 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 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 Show quote "Steve Fortner" wrote: > 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 > >  |
|||||||||||||||||||||||
Other interesting topics