July 19, 2017 at 11:07 am
Server: SQL 2014
XML knowledge: Beginner
We are tasked with running an SQL query on a table andgenerate an XML file to the local disk on the server. We cannot use the xp_cmdshell because we keepit disabled.
In our lab where xp_cmdshell is enabled we have createdthe a script SQLCMD ... Select … path (''…''), ROOT(''…''), ELEMENTS" 'and it generates the XML file and we can open it in IE and everything looksgreat.
Since we have to run this daily and cannot usexp_cmdshell I figured we could create an Agent Job and in the step I wouldplace the script Select … path (''…''), ROOT(''…''), ELEMENTS" ' and tohave it generate a file I go to advanced and in Output file: I haveS:\folder\filename.xml
Here is the problem. I get a file and it has the XML garble, but it does not open in IE.
I also see that the file has a header (job name, step #, …)before the XML garble and at the end is has (10 rows(s) affected). I tried to remove these things and it stillwill not open in IE. I spoke to theapplication owner and he seems to think that if you cannot open it in IE the file is not well formatted and wont work.
I compared the XML garble from the two files (with CMD andwithout CMD) and from the first few lines they are exactly the same. The developer will be using “Beyond Compare”to see if they truly exact all the way through.
Q: How can I just get a nice XML file using the SQL Agentjob?
Q: How can i use the output file in the Agent job without the header and the footer mentioned above.
Any feedback is appreciated.
Jeff
July 19, 2017 at 12:56 pm
jayoub i think you have to avoid using sqlcmd in this case, and create a simple SSIS package isntead.
I knew two things right away that were affecting you:
you needed the sqlcmd -h-1 to remove headers, and you needed an explicit SET NOCOUNT ON in your query.
I tested sqlcmd in powershell like this, and at row 256, sqlcmd wrapps the xml document with line breaks, which breaks the xml.
i tried using the screenwidth flag, but it made no difference.
my environment is SQL2016 Developer;
you would avoid the whole issue with SSIS,a si t would be able to output the returned value without second guessing the formatting.
here's my sqlcmd prototype:
[string] $sqlcmd = @"
SET NOCOUNT ON;
SELECT
object_schema_name(colz.object_id) As SchemaName,
object_name(colz.object_id) As ObjectName,
colz.*
FROM master.sys.columns colz
FOR XML PATH('Root') ,ELEMENTS
"@
$out = "C:\Data\OUTPUT\MyXml.xml" ;
#invoke-sqlcmd -InputFile $f.fullname | format-table | out-file -filePath $out
#Flags:
# -S server\instancename
# -E = trusted connection
# -i input file
# or
# -q for an INTERACTIVE inline query
# -Q cmd line query and exit
# -I Enable Quoted Identifier: critical for stored proc creations
# -h headers -1 means remove headers
# -w screen width to avoid auto wrapping in the midle of xml
sqlcmd -S "(local)" -E -I -h-1 -w65535 -Q $sqlcmd | out-file -filePath $out
Lowell
July 19, 2017 at 1:26 pm
Thank you very much for your help
My team lead is already working on the SSIS package and should be getting that to work.
When we did a beyond compare to the XML generated using CMD and the one generated with the Agent and there were character returns that would break the file.
I also tried using powershell with the export-clixml cmdlet and it gave me an XML, but the formatting was off. We tried with and w/o AUTO
Its weird how SQL server cannot generate a good XML file from the agent job,
I will continue to try using powershell to get this working - just in case we have issue with SSIS and I like learning new things.
Again thank you very much for the quick reply
Jeff
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply