Home Forums SQL Server 2014 Development - SQL Server 2014 Automated XML file RE: Automated XML file
February 1, 2018 at 6:43 am
durocshark - Wednesday, January 31, 2018 10:54 AMI 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.
Thanks!
We'd tried to use SSIS, Problem is we need to write .net code to do the automation.