Data Access Application Block

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How would I use it to execute an SQL stored procedure that returns flat xml
What are good resources to learn how to use Data Access Application Block

Thank
 
I'm not sure what you mean by flat XML File but you can use the FOR XML Raw
to output your results to XML without formatting. It's not a very useful
format in most regards...but you can specify it in your SQL Statement , just
like FOR XML AUTo or any of the other XML features.

ANother thign you can do is pull back a DataSet and use its .WriteXML method
t accomplish this...it's very easy and SqlHelper already returns DataSets if
I remember correctly.

I'm not sure of any specific links for showing you how to use it other than
the stuff on GotDotNEt, but the class is pretty straightforward. Is there
something in particular that's giving you trouble, perhaps I could help you
through it. (I'll look around for some links and post them if I come across
any).

Cheers,

Bill
 
Thanks for help
I see what you are saying
Here what I have so far
My SP has three select statements: (simplified
select <root
select data for xml aut
select </root
My undestanding of ADO is that the result should be a single string
That's not what I am getting
I have something like this
- <Table><Column1><ROOT></Column1></Table
on a top of my xml, then three sets of
- <Table1><XML_F52E2B61-18A1-11d1-B105-00805F49916B><.... some data....</XML_F52E2B61-18A1-11d1-B105-00805F49916B></Table1
- <Table1><XML_F52E2B61-18A1-11d1-B105-00805F49916B><.... some data....</XML_F52E2B61-18A1-11d1-B105-00805F49916B></Table1
- <Table1><XML_F52E2B61-18A1-11d1-B105-00805F49916B><.... some data....</XML_F52E2B61-18A1-11d1-B105-00805F49916B></Table1
then
- <Table2><Column1></ROOT></Column1></Table2
How can I have a xml single string

TIA
 
Mark, If you have three select statements ,you are going to have three
datatables in your dataset. If you are using the Select <ROOT> and Select
</ROOT> just to have the document structured, there are other ways to
accomplish it. As far as the ADO aspect of it goes, ADO.NET is simply going
to return what the DB returns and depending on the object you use to
retrieve the query, you'll get vastly different results. If you can the Root
select statements, I think you can just use the For XML Raw predicate and
then string s = cmd.ExecuteScalar(); . I don't access my XML like this but I
believe it will work in that s will now be the one xml doc.

There are some really powerful tools for dealing with XML though and you can
use an XML Reader for instance and just walk through it, appending each tag
to a stringbuilder for instance and then use Stringbuilder.ToString() and
you could force it all into one string object. Hit google for XMLReader and
you'll find a lot of good examples...If I have some free time tonight I'll
try to put a couple of different samples together for you.

Cheers,

Bill
Mark said:
Thanks for help.
I see what you are saying.
Here what I have so far.
My SP has three select statements: (simplified)
select <root>
select data for xml auto
select </root>
My undestanding of ADO is that the result should be a single string.
That's not what I am getting.
I have something like this:
- <Table><Column1><ROOT></Column1></Table>
on a top of my xml, then three sets of:
- <Table1><XML_F52E2B61-18A1-11d1-B105-00805F49916B><.... some
Click to expand...
data.... said:
- <Table1><XML_F52E2B61-18A1-11d1-B105-00805F49916B><.... some
Click to expand...
 
Use SQLXML
(http://www.microsoft.com/downloads/details.aspx?FamilyID=4C8033A9-CF10-4E22
-8004-477098A407AC&displaylang=en). This gives you a set of managed classes
for retrieving XML results from SQL Server 2000 in exactly the way you
describe. You'd need to change your sproc to simply return the data (without
the <root> query), and specify the RootTag property of the SqlXmlCommand
class used to execute it.

You could also try posting any follow-up questions in
microsoft.public.sqlserver.xml.

If you want more info, I can recommend a good book on SQL Server and XML
(http://www.microsoft.com/mspress/books/6137.asp) - I could do with the
royalties ;-)

Cheers,
Graeme

--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com


Mark said:
Thanks for help.
I see what you are saying.
Here what I have so far.
My SP has three select statements: (simplified)
select <root>
select data for xml auto
select </root>
My undestanding of ADO is that the result should be a single string.
That's not what I am getting.
I have something like this:
- <Table><Column1><ROOT></Column1></Table>
on a top of my xml, then three sets of:
- <Table1><XML_F52E2B61-18A1-11d1-B105-00805F49916B><.... some
Click to expand...
data.... said:
- <Table1><XML_F52E2B61-18A1-11d1-B105-00805F49916B><.... some
Click to expand...
 
Of course, to do this using the DAAB, you'd have to customize the SqlHelper
class by adding a new method that uses the SqlXmlCommand class (call it
ExecuteSqlXmlStream or something and deploy the SqlXml assembly with the
DAAB.)
 
Back
Top