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

Generate an XML file from a 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 writet
oxmlfile. 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.
================================================== ====================
*** I need to generate an xml file. How do I run a stored procedure and have the results as an xml file?
================================================== ====================
Thanks for the help.
Tony
One way I can think of is
in QA, Set the "Results to file" option.
Roji. P. Thomas
SQL Server Programmer
"CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:02CBE611-33FB-4ADD-A70D-6618C7448101@.microsoft.com...
> 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.
> ================================================== ====================
> *** I need to generate an xml file. How do I run a stored procedure and
have the results as an xml file?
> ================================================== ====================
> Thanks for the help.
> Tony
>
|||Assuming the stored procedure actually returns XML (i.e. it uses a FOR XML
clause), you could easily write a VB Script to do this as shown in the
following example. For this to work you need to install SQLXML 3.0 on the
client from which you'll run the script:
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Const adCmdStoredProc = 4
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
"SERVER=MyServer;DATABASE=MyDB;INTEGRATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
'Specify the root tag
cmd.Properties("xml root") = "QueryResults"
'Create DOMDocument object for results.
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML2.DOMDocument")
'Assign the output stream.
cmd.Properties("Output Stream") = xmlDoc
'Specify the command
cmd.CommandText = "MyStoredProc"
cmd.CommandType = adCmdStoredProc
'Execute the command returning a stream
cmd.Execute, , adExecuteStream
' Save the file
xmlDoc.Save("C:\Results.xml")
MsgBox("File saved")
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:02CBE611-33FB-4ADD-A70D-6618C7448101@.microsoft.com...
> 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.
> ================================================== ====================
> *** I need to generate an xml file. How do I run a stored procedure and
have the results as an xml file?
> ================================================== ====================
> Thanks for the help.
> Tony
>
|||Thanks for your help, but I don't have SQLXML. I don't even know what that is.
I tried outputting the file by setting QA to output results to a file. It works, but the file is not a real xml file. I am using FOR XML and get the results in the text in the results pane that looks like XML. I need an actual xml file.
Tony
|||SQLXML is the name of the XML mid-tier component that comes with SQL Server
2000 as part of OLEDB. See Books Online for more information. Later versions
of the mid-tier component have been made available for free as web downloads
(see http://msdn.microsoft.com/sqlxml).
The easiest way to programmatically get your FOR XML result into a file is
to write an ADO script that retrieves the FOR XML result as a result stream
(not a recordset!). You can associate the file's stream as the result
stream, so you should get the data directly into the file.
Best regards
Michael
"CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:7D7C7E80-D8EE-40F1-9064-F6CCFBE2A29B@.microsoft.com...
> Thanks for your help, but I don't have SQLXML. I don't even know what
> that is.
> I tried outputting the file by setting QA to output results to a file. It
> works, but the file is not a real xml file. I am using FOR XML and get
> the results in the text in the results pane that looks like XML. I need
> an actual xml file.
>
> Tony
|||Download SQLXML 3.0 from
http://www.microsoft.com/downloads/d...isplaylang=en,
install it on your client, and modify the script in my previous post to
access your server / database and execute your FOR XML stored procedure.
Hopefully that should do it.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:7D7C7E80-D8EE-40F1-9064-F6CCFBE2A29B@.microsoft.com...
> Thanks for your help, but I don't have SQLXML. I don't even know what
that is.
> I tried outputting the file by setting QA to output results to a file. It
works, but the file is not a real xml file. I am using FOR XML and get the
results in the text in the results pane that looks like XML. I need an
actual xml file.
>
> Tony