I was given this code from a vendor to export customer data from our ERP system into their system. I need to automate this and save it as a formatted (with CR's) XML file.
SELECT TOP (100) PERCENT Customer_1.Name AS BilltoName, dbo.Address.Address1 AS BillToAddress, dbo.Address.City AS BillToCity, dbo.Address.Zip AS BillToZip,
dbo.Address.State AS BilltoState, dbo.Customer.Name AS ShiptoName, Address_1.Address1 AS ShipToAddress, Address_1.City AS ShipToCity, Address_1.Zip AS ShipToZip,
Address_1.State AS ShipToState, dbo.Customer.DefManufacturingPlantLink, dbo.Customer.BillToLink, dbo.Customer.CustomerLink AS shiptolink,
dbo.Customer.CustomerShortName AS ShortName
FROM dbo.Customer INNER JOIN
dbo.Customer AS Customer_1 ON dbo.Customer.BillToLink = Customer_1.CustomerLink INNER JOIN
dbo.Address ON Customer_1.CustomerLink = dbo.Address.CustomerLink INNER JOIN
dbo.Address AS Address_1 ON dbo.Customer.CustomerLink = Address_1.CustomerLink
WHERE (dbo.Customer.DefManufacturingPlantLink <> 2)
for xml path ('Customer'), root ('Customers')
This code works perfectly fine in SSMS as a straight query. But I can't keep running this manually and need to save it as an XML file on a network file share.
I'm a total n00b and have no idea where to even start. I can make it a SQL Server Agent Job, but don't know what to add to make it save the output.