So I have three SQL queries and when I run them in SQL Management studio with the FOR XML AUTO, ELEMENTS; line, they generate an XML file with everything I am looking for.
Now, I want to automate these queries to run every hour and save the results as a XML file (another program is going to take these files and ingest them). So I made a .bat file with sqlcmd that calls in the queries and saves the results to a folder. The folder gets created and I get three seperate XML files, but when you open them there is only a little bit of data. It's like it only grabbed the first couple pieces in each query and then quit. Also, the formatting is not neat and tiday at all compared to how it looked when viewing it in SQL Studio.
Code is below, sensitive stuff has been censored with xxx:
@echo off
setlocal
REM Set SQL Server connection details
set ServerName=xxxx
set DatabaseName=xxx
set Username=xxx\xxxx
set Password=xxx
REM Set the output folder
set OutputFolder=C:\Users\xxxxx\Desktop\Results
REM Ensure the output folder exists
if not exist %OutputFolder% mkdir %OutputFolder%
REM Run the booking query and save results as XML
sqlcmd -S xxx.xxx.xx.xxx -i C:\Users\xxxx\Desktop\Interface\booking.sql -W -o "%OutputFolder%\booking.xml"
REM Run the release query and save results as XML
sqlcmd -S xx.xx.xx.xx -i C:\Users\xxxx\Desktop\Interface\release.sql -W -o "%OutputFolder%\release.xml"
REM Run the incident query and save results as XML
sqlcmd -S xx.xx.xx.xxx -i C:\Users\xxxx\Desktop\Interface\incident.sql -W -o "%OutputFolder%\incident.xml"
echo Queries executed successfully. XML files saved to %OutputFolder%
pause
Is there anything I am missing? Or does anyone know why running this .bat file would only save a fraction of the results it would normally pull in SQL to the XML?
Tried redoing the whole thing with BCP but that made things even worse. Just got a 0 byte file that was no file type at all. Expecting the same amount of results as I get when I run these queries in SQL Studio