Save the resultant XML in server

Question

Save the resultant XML in server

Jagan Kumar

SSC Eights!

Points: 805
More actions
March 4, 2005 at 3:14 pm

#102415

Hi all,
I want to save the results of ,
Select *
FROM Emp
FOR XML AUTO, ELEMENTS
in an XML file using a stored procedure.
I dont want to use any application programs, like ADO stream.
Please help
KJK
March 7, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

As far as I know, the ONLY way to save your xml is using an application stream for SQL 2000. SQL 2005, with it's more robust XML support, may offer you a solution once it is out of Beta.

If you try to use a cursor, or insert into a table in SQL 2000, you'd get one of these errors:

The FOR XML clause is not allowed in a CURSOR statement.

The FOR XML clause is not allowed in a INSERT statement.

Since you can't use a cursor to step thru the results, nor dump the results into a table for manipulation, the possibilities for using the data are limited. Trying not to use a stream object pretty much cuts off all possibilities, I think.

Potentially, the xml returned could be larger than a varchar(8000) anyway, so you'd see truncation in doing it on SQL Server , right?

Using ADO you can easily grab it all , dump it into an xml document object for manipulation, and dump it into a file with the FilesystemObject. see the bol and search for "Retrieving Result Sets into Streams" for an example.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jayaram Krishnaswamy SSC Eights! Points: 980 More actions · Answer 2

Send your query as a URL query and then save the browser output to a file name with .xml extension.

Stored procedures can also be used for url queries as in this memo:

------------------------

It is also possible to access a stored procedure from an URL. Consider the following stored procedure on the SQL Server in th pubs database. Since a stored procedure needs to be executed, the object, namely,MySimpleXML2 must be configured such that the user accessing the procedure has the permissions.

Create procedure MySimpleXML2 @State char(2)asSelect stor_name, city, zip, statefrom storeswhere state=@Stateorder by city for XML auto

The following query can be used to access this stored procedure and the result is shown in the next picture. http://xphtek/PubsVirtual/?sql=MySimpleXML2+'CA'&root=root

The browser output is xml which can be saved to a file if needed.

---------------------------

Gabriela Nanau SSCommitted Points: 1967 More actions · Answer 3

The bcp seems to work fine.

I used something like this:

exec master..xp_cmdshell 'bcp "Select * FROM Northwind.dbo.orders FOR XML AUTO, ELEMENTS" queryout "c:\testOrdersxml.txt" -SWS285\gnTEST -c -T'

Gabriela