Generate XML and save to file

My question is quite simple, but I haven't been able to find any good
resources on web yet so I thought I'd give it a try here;
Is it possible to generate an xml file using the "FOR XML EXCPLICIT"-clause
and save the generated xml as a xml-file?
sincerely,
Kjetil KlaussenHi Kjetil,
See at this bol address:
mk:@.MSITStore:C:\Archivos%20de%20program
a\Microsoft%20SQL%20Server\80\Tools\
Books\xmlsql.chm::/ac_openxml_4y91.htm#Feedback
"Kjetil Klaussen" wrote:

> My question is quite simple, but I haven't been able to find any good
> resources on web yet so I thought I'd give it a try here;
> Is it possible to generate an xml file using the "FOR XML EXCPLICIT"-claus
e
> and save the generated xml as a xml-file?
> sincerely,
> Kjetil Klaussen|||Hi, Enric
Did you mean the example "Obtain an XML document consisting of customers,
orders, and order details"? Can't see how this would help me out here...
My problem is not to generate the XML - that part is ok. But is it possible
to save the result of an "SELECT ... FOR XML EXPLICIT" to a file?
Using e.g. the 'Scripting.FileSystemObject' and executing 'sp_OAMethod' I
can create a file and fill it with text directly from SQL Server. The proble
m
is that to be able to use 'sp_OAMethod' I need to place the result from the
"SELECT ... FOR XML EXPLICIT" into a local variable, but TEXT cannot be use
d
as local variables in t-sql (VARCHAR would not be satisfying as I don't know
the size of the result).
"Enric" wrote:
> Hi Kjetil,
> See at this bol address:
> mk:@.MSITStore:C:\Archivos%20de%20program
a\Microsoft%20SQL%20Server\80\Tool
s\Books\xmlsql.chm::/ac_openxml_4y91.htm#Feedback
> "Kjetil Klaussen" wrote:
>|||In SQL 2000?
You need an XML client. If you set up a virtual SQL-XML server, you can use
your browser to build the XML, then save it to a file as you would any other
web page. Or you can design your own client.
In SQL 2005?
Save the XML result-set into a file. Or use BCP.
ML|||I guess this would mean that the xml is generated on a request from the
virtual SQL-XML server? This wouln't solve my problem either I'm afraid...
Maybe I should add some more info on what I'm really trying to do:
When an update/insert/delete-operation is performed on a specific table, I
want to generate a xml-file and place this xml-file on our webserver. The wa
y
I thought I'd do it was to define triggers on the table and make the trigger
run a stored procedure to generate the xml-file based on a "SELECT ... FOR
XML EXPLICIT" query. This xml-file would then be bound to a webpage (.Net
1.1) through a file dependency. The webpage would be implemented using AJAX
(asynchronous java-script and xml) and instantly refresh the page content
when the xml-file changes. Wheather this is even possible using .Net 1.1 and
SQL Server 2000, I don't know yet.
The solution might be to upgrade the webpage to .Net 2.0 and use
SqlCacheDependency instead...
If anyone have any thoughts regarding this, I'm looking forward to hear your
objections and replies :)
Sincerely
Kjetil Klaussen
"ML" wrote:

> In SQL 2000?
> You need an XML client. If you set up a virtual SQL-XML server, you can us
e
> your browser to build the XML, then save it to a file as you would any oth
er
> web page. Or you can design your own client.
>
> In SQL 2005?
> Save the XML result-set into a file. Or use BCP.
>
> ML|||IMHO you'd be better off using SQL Notification Services, since a trigger
such as this might end up having a serious impact on performance.
ML|||I guess this would mean that the xml is generated on a request from the
virtual SQL-XML server? This wouln't solve my problem either I'm afraid...
Maybe I should add some more info on what I'm really trying to do:
When an update/insert/delete-operation is performed on a specific table, I
want to generate a xml-file and place this xml-file on our webserver. The wa
y
I thought I'd do it was to define triggers on the table and make the trigger
run a stored procedure to generate the xml-file based on a "SELECT ... FOR
XML EXPLICIT" query. This xml-file would then be bound to a webpage (.Net
1.1) through a file dependency. The webpage would be implemented using AJAX
(asynchronous java-script and xml) and instantly refresh the page content
when the xml-file changes. Wheather this is even possible using .Net 1.1 and
SQL Server 2000, I don't know yet.
The solution might be to upgrade the webpage to .Net 2.0 and use
SqlCacheDependency instead...
If anyone have any thoughts regarding this, I'm looking forward to hear your
objections and replies :)
Sincerely
Kjetil Klaussen
"ML" wrote:

> In SQL 2000?
> You need an XML client. If you set up a virtual SQL-XML server, you can us
e
> your browser to build the XML, then save it to a file as you would any oth
er
> web page. Or you can design your own client.
>
> In SQL 2005?
> Save the XML result-set into a file. Or use BCP.
>
> ML|||The performance is not the issue here. This is a table that will be updated
10-20 times a day, but when the update comes the new data must be given to
the client as fast as possible. This is why I don't want to have a client or
IIS polling the database for changes all the time - this would probably be
worse as regards to the performance issue . I'd rather have the SQL Server
push the information to the IIS.
My first intention was to use the 'sp_makewebtask' and make this generate
the XML-file. This is essentially the same as I'm trying to do manually, but
as 'sp_makewebtask' is depreciated and will not be supported in SQL Server
2005, I didn't want to implement my 'push'-mechanism that way. So I started
examining the notification services a bit, but finding out that notification
services projects don't get ported to VS 2005 (as far as I know), this is no
t
an option either.
But regardless of my further investigations and choice of implementation,
I'm still curious about my original question; Is it possible to generate an
xml file in a stored procedure (preferably using the "FOR XML
EXCPLICIT"-clause) and save the generated xml to a file?
Sincerely,
Kjetil Klaussen
"ML" wrote:

> IMHO you'd be better off using SQL Notification Services, since a trigger
> such as this might end up having a serious impact on performance.
>
> ML