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 report from list of parameters?

I have one report with two datasets, and consequently two queries. The first
one gets a list of ReportID's for a particular day, while the second dataset
uses each ReportID to create a report.
Query 1:
SELECT ReportID
FROM Table1
WHERE Date=@.Date
Query 2:
SELECT *
FROM *A whole bunch of tables*
WHERE ReportID = @.ReportID
If I try to Union '(All)' to Query1, Query2 will return info for all
ReportID's in the system, not just what was generated in Query1. I also
tried creating a comma delimited field of all the ReportID's and using WHERE
ReportID IN @.ReportID, to no avail. How can I generate multiple reports for
Query2 while only running Query1 once?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1On Feb 28, 6:28 am, "ana9" <u28955@.uwe> wrote:
> I have one report with two datasets, and consequently two queries. The first
> one gets a list of ReportID's for a particular day, while the second dataset
> uses each ReportID to create a report.
> Query 1:
> SELECT ReportID
> FROM Table1
> WHERE Date=@.Date
> Query 2:
> SELECT *
> FROM *A whole bunch of tables*
> WHERE ReportID = @.ReportID
> If I try to Union '(All)' to Query1, Query2 will return info for all
> ReportID's in the system, not just what was generated in Query1. I also
> tried creating a comma delimited field of all the ReportID's and using WHERE
> ReportID IN @.ReportID, to no avail. How can I generate multiple reports for
> Query2 while only running Query1 once?
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1
If I'm understanding you correctly, you should be able to do one of a
few things:
Option 1:
SELECT *
FROM *A whole bunch of tables*
WHERE ReportID in (SELECT ReportID
FROM Table1
WHERE Date=@.Date )
Option 2:
Create a cursor (or while loop) to loop through Query 1 and execute
Query 2 in the loop for each ReportID (possibly populating a single
dataset or multiple datasets).
Ofcourse Option 1 is far less performance intensive. If I'm not
understanding you correctly, let me know.
Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||That was very helpful, thank you. Option1 is a variation of what I had tried
before. The only issue (which I failed to mention) is that Query2 is
actually a report that gets populated from about 20 datasets. So each
dataset may not return the exact same number of rows, in which case some of
the information will not be displayed in the correct manor. Also, for some
of those datasets I use SELECT Top # so using Option1 will not allow me to
get the correct amount of results.
Your suggestion for Option2 seems like it would be perfect for this scenario,
however I was under the impression that you can't create while loops in SQL.
Could you point me in the direction of some resources/examples? Thank you.
EMartinez wrote:
>> I have one report with two datasets, and consequently two queries. The first
>> one gets a list of ReportID's for a particular day, while the second dataset
>[quoted text clipped - 18 lines]
>> --
>> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1
>If I'm understanding you correctly, you should be able to do one of a
>few things:
>Option 1:
>SELECT *
>FROM *A whole bunch of tables*
>WHERE ReportID in (SELECT ReportID
>FROM Table1
>WHERE Date=@.Date )
>Option 2:
>Create a cursor (or while loop) to loop through Query 1 and execute
>Query 2 in the loop for each ReportID (possibly populating a single
>dataset or multiple datasets).
>Ofcourse Option 1 is far less performance intensive. If I'm not
>understanding you correctly, let me know.
>Hope this is helpful.
>Regards,
>Enrique Martinez
>Sr. SQL Server Developer
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1|||You might also try
SELECT ReportID INTO #Reports
FROM Table1
WHERE Date=@.Date
SELECT *
FROM *A whole bunch of tables*
inner join #Reports
On ReportID = #Reports.ReportID
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"ana9" wrote:
> I have one report with two datasets, and consequently two queries. The first
> one gets a list of ReportID's for a particular day, while the second dataset
> uses each ReportID to create a report.
> Query 1:
> SELECT ReportID
> FROM Table1
> WHERE Date=@.Date
> Query 2:
> SELECT *
> FROM *A whole bunch of tables*
> WHERE ReportID = @.ReportID
> If I try to Union '(All)' to Query1, Query2 will return info for all
> ReportID's in the system, not just what was generated in Query1. I also
> tried creating a comma delimited field of all the ReportID's and using WHERE
> ReportID IN @.ReportID, to no avail. How can I generate multiple reports for
> Query2 while only running Query1 once?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200702/1
>

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