Generate XML file from stored procedure

I need to develop some crystal reports on some .NET ado datasets.
This is easy to do if I actually had data to work with. It is much
easer creating reports with you have data.

Therefore, I would like to run the stored procedure that the .NET ado
dataset is built on and generate an XML file. I could run the project
and try to figure out where the developers are making the call to the
stored procedure and insert a line to writetoxmlfile. I would rather
not have to mess with their code.

Is there a way working with SQL Server (either query analyzer or
enterprise manager, dts, or whatever) that I can generate an xml file.

I see that I can run a stored procedure and get an xml style return in
query analyzer, but I don't know how to save that as an actual file.

Thanks for the help.

Tony"CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:b0fe186f.0405100539.3807ad32@.posting.google.c om...
> I need to develop some crystal reports on some .NET ado datasets.
> This is easy to do if I actually had data to work with. It is much
> easer creating reports with you have data.
> Therefore, I would like to run the stored procedure that the .NET ado
> dataset is built on and generate an XML file. I could run the project
> and try to figure out where the developers are making the call to the
> stored procedure and insert a line to writetoxmlfile. I would rather
> not have to mess with their code.
> Is there a way working with SQL Server (either query analyzer or
> enterprise manager, dts, or whatever) that I can generate an xml file.
> I see that I can run a stored procedure and get an xml style return in
> query analyzer, but I don't know how to save that as an actual file.
> Thanks for the help.
> Tony

I'm not entirely sure what you're looking for, but you can save the results
from Query Analyzer to a file, or use DTS or osql.exe to write the results
to file automatically. You would probably need to process the file further.
Alternatively, you may be looking for the FOR XML clause of the SELECT
statement - there are examples of using this in Books Online.

If this isn't helpful, perhaps you can give some more specific details of
what you need to achieve.

Simon|||I need an xml file.

When I execute a stored procedure with parameters, I need to output an
actual xml file.

Thanks,

Tony|||"CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:b0fe186f.0405110638.41b97942@.posting.google.c om...
> I need an xml file.
> When I execute a stored procedure with parameters, I need to output an
> actual xml file.
> Thanks,
> Tony

From your previous post, you seem to have a procedure which returns XML,
presumably using the FOR XML clause. In that case, you have to use some
sort of client program to get the results into a file. For example, you
could use osql.exe:

osql.exe -S Server -d Database -E -Q "exec myProc" -o outputfile.txt

Or you can use DTS, or write your own client script using ADO COM objects.
But I believe that even with FOR XML, you get a fragment, not a well-formed
XML document, so you would still need to do some more work with the file to
get a real XML document. If you're using the ADO Command object, for
example, you need to use the Command object's "XML Root" property to add a
root which is then wrapped round the results.

But XML is a big topic, and there are other ways to get XML out of SQL
Server. You might want to post in microsoft.public.sqlserver.xml for more
information.

Simon