Saving XML output automatically

post the query
dts should work fine

Jay

HERE IS a simple ASP Example

<%
Option Explicit

Response.ContentType = "text/xml"

Dim ObjADORS
Const adPersistXML = 1

Set ObjADORS = Server.CreateObject("ADODB.Recordset")

ObjADORS.Open "SELECT ContactName FROM Customers", _
      "PROVIDER=SQLOLEDB.1;SERVER=.;UID=sa;PWD=;DATABASE=Northwind;"

ObjADORS.Save Response, adPersistXML

ObjADORS.Close
Set ObjADORS = Nothing
%>

sp_makewebtask  is by far the easiest way to do this.   If youve got permissions problems with this then regardless, you will end up with permissions problems on any other method.

I've simplified everything, heres the query followed by the results when run in query analyser.

SELECT      top 2 RTRIM(SOPNUMBE) 'InvoiceNo', RTRIM(ORIGNUMB) 'OrderNo', ACTLSHIP 'Shipped', RTRIM(CUSTNMBR) 'CustomerNo', RTRIM(CSTPONBR) 'CustomerPO'
  FROM      SOP30200 As DespatchNotes
  WHERE      (SOPTYPE = '3')
  for xml auto, xmldata, elements

<Schema name="Schema9" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="DespatchNotes" content="eltOnly" model="closed" order="many">
    <element type="InvoiceNo"/>
    <element type="OrderNo"/>
    <element type="Shipped"/>
    <element type="CustomerNo"/>
    <element type="CustomerPO"/>
  </ElementType>
  <ElementType name="InvoiceNo" content="textOnly" model="closed" dt:type="string"/>
  <ElementType name="OrderNo" content="textOnly" model="closed" dt:type="string"/>
  <ElementType name="Shipped" content="textOnly" model="closed" dt:type="dateTime"/>
  <ElementType name="CustomerNo" content="textOnly" model="closed" dt:type="string"/>
  <ElementType name="CustomerPO" content="textOnly" model="closed" dt:type="string"/>
</Schema>
<DespatchNotes xmlns="x-schema:#Schema9">
  <InvoiceNo>INV6000057</InvoiceNo>
  <OrderNo>ORD6000116</OrderNo>
  <Shipped>2001-05-10T00:00:00</Shipped>
  <CustomerNo>LAN001</CustomerNo>
  <CustomerPO>PO044423</CustomerPO>
</DespatchNotes>
<DespatchNotes xmlns="x-schema:#Schema9">
  <InvoiceNo>INV6000058</InvoiceNo>
  <OrderNo>ORD6000122</OrderNo>
  <Shipped>2001-05-10T00:00:00</Shipped>
  <CustomerNo>PRO001</CustomerNo>
  <CustomerPO>PCN0510/PF1</CustomerPO>
</DespatchNotes>

This is some of what I get in the text file;
440949006E0076006F006900630065004E006F0044074F0072006400650072004E006F00440753006800690070007

Thanks for all the comments.
I'm trying the VB script first. I like the oSQL solution too.

I can't believe it is such a pain though.

The sp_makewebtask worked provided the script was run on the same server as the database and the output saved to that server, if the query is distributed or I save to a UNC path if get 'Remote access not allowed for Windows NT user activated by SETUSER.' errors even though.

Hilaire  -- Very intersting.. do you know of a reference that documents this technique?

Jay

Forgot to say thanks to you all.
Hilaire's approach proved to be the most elegant.

Regards
Chris McGuigan

@Jay
>>do you know of a reference that documents this technique? <<
No sorry, my references are SQL Server Books OnLine (AKA Transact-SQL help) and EE Knowledge base.
I built this one a while ago for an EE member that needed to get FOR XML output inside a T-SQL variable.
I happen to use sp_OA* methods to send mail whithout bothering with SMTP relay , outlook account, ...

The most tricky thing with the sample code above is to call methods that return objects.
You'll find more exemples on the EE boards.

Regards

Hilaire

one minor enhancement to hill solution may be to test for file existance  

with xp_fileexist stored proc

rather than exec master..xp_cmdshell 'type "c:\temp\test2.xml"'