SQLTeam.com | Weblogs | Forums

How save query with xml output


#1

Hello everyone,

I have a query with xml results as output. But in this query I want to save this xml file to a local folder (eg. c:/temp) so I can schedule this query. I have tried to fix it in the job itself to save it as a file but then the XML is not the same as in the query. And it must be the same as in the query below because that is the way it must be imported in another program.
I'm using the following query:

select
replace (sum (abs(convert(decimal(10,2), amountpaid))), ',', '.') as amountpaid,
date as date,
journal,
finyear,
invoice,
currency,
customer,
from payments
where journalized > GETDATE() -1
group by date, journal, finyear, invoice, currency, customer
for XML PATH('customerpayment'),
ROOT ('customerpayments')

thanx.


#2

How are you saving it in the job?
How does the XML differ?


#3

In the job and then 'advanced' I have set the output file to c:\temp\payments.xml

When I'm opening this file everything is in a row and doesn't look like a xml layout file. Also the job name and other stuff is written in this file. When i'm opening the file from the sql query it is just an xml file. I want that file written to c:\temp