EXPORTING SQL.XML data to file

I have a similar problem to that of Barry van Dijk
I need to save SQL.2000 Invoice Data as an XML structured file in a
designated directory for FORMSCAPE to {print|e-mail|fax + archive}, each
filename needs to be based on INVOICE number.
Whilst I understand Select .. FOR XML, doing something v.useful with it
{save to file} is taxing my brain! This process must be automatic (stored
proc.)
Simon Bateman
In SQL Server 2000, it is not easy to save any data to a file in the server.
Recommended is to write a small client app that streams the result stream of
the ADO/ADO.Net XML result generated by the FOR XML into a file stream.
If it really has to be done on the server, in SQL Server 2000, you would
have to call the above client app via the sp_OA stored procedures. In SQL
Server 2005, you can use the CLR to write a user-defined function to do so.
Best regards
Michael
"SimonBA" <SimonBA@.discussions.microsoft.com> wrote in message
news:A93EAF21-F891-4897-96BE-AC5184B47B42@.microsoft.com...
>I have a similar problem to that of Barry van Dijk
> I need to save SQL.2000 Invoice Data as an XML structured file in a
> designated directory for FORMSCAPE to {print|e-mail|fax + archive}, each
> filename needs to be based on INVOICE number.
> Whilst I understand Select .. FOR XML, doing something v.useful with it
> {save to file} is taxing my brain! This process must be automatic (stored
> proc.)
> --
> Simon Bateman