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