Post reply

Automated XML file

  • January 31, 2018 at 10:54 am

    #410148

    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.

    Thanks!

  • January 31, 2018 at 12:24 pm

    #1978079

    You can use BCP to export the XML to the file system
    😎

  • January 31, 2018 at 1:24 pm

    #1978091

    Eirikur Eiriksson - Wednesday, January 31, 2018 12:24 PM

    You can use BCP to export the XML to the file system
    😎

    Thanks I've seen that mentioned but I can't find a walkthrough that works with my limited skills.

  • February 1, 2018 at 6:43 am

    #1978174

    durocshark - Wednesday, January 31, 2018 10:54 AM

    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.

    Thanks!

    We'd tried to use SSIS, Problem is we need to write .net code to do the automation.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply