June 20, 2011 at 6:50 pm
Hi there,
I am trying to export some xml data from sql server via sqlps. I want it to run in a sql agent job. The xml is generated from a stored procedure. I am able to get the complete result set out but it comes with line breaks at each line and extra breaks at the end of the file which makes the xml invalid.
Is there a way to do this aside from manipulating the file after export?
This is what I have:
invoke-sqlcmd -serverinstance sqlserv -database playground "exec dbo.myproc"|format-table -hidetableheaders |out-file "C:\Temp\myfile.xml" -width 2034
Thanks!
June 20, 2011 at 7:08 pm
Not sure I understand? Line breaks are valid in XML, and "line breaks at the end of the file", should be outside of the root element, and thus not part of the XML.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 20, 2011 at 8:04 pm
michanne (6/20/2011)
Hi there,I am trying to export some xml data from sql server via sqlps. I want it to run in a sql agent job. The xml is generated from a stored procedure. I am able to get the complete result set out but it comes with line breaks at each line and extra breaks at the end of the file which makes the xml invalid.
Is there a way to do this aside from manipulating the file after export?
This is what I have:
invoke-sqlcmd -serverinstance sqlserv -database playground "exec dbo.myproc"|format-table -hidetableheaders |out-file "C:\Temp\myfile.xml" -width 2034Thanks!
I think your syntax is incorrect. The "-width 2034", I think, is adding the extra line breaks. Does the proc produce the output in XML?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 21, 2011 at 4:55 pm
Thanks for the quick replies.
Not sure I understand? Line breaks are valid in XML, and "line breaks at the end of the file", should be outside of the root element, and thus not part of the XML
The results export in as an object array chunked into equal lengths so the breaks can be in the tag. So $a[1] produces the second record which starts in the middle of the tag. I've been looking for some way to keep it in a single string (stream?) but nothing has worked so far. Am I missing it - seems like it should be obvious!
I think your syntax is incorrect. The "-width 2034", I think, is adding the extra line breaks. Does the proc produce the output in XML?
The proc is definitely outputting xml data.
2034 is the width of the object array. If I increase the width I get padding and line breaks. If I decrease the width it truncates the output. I understand the default width is based on the host profile which is 80 unless you change it. I haven't figured out how the 'object' width is determined or if that is even the right direction.
Thanks!
June 24, 2011 at 1:10 am
June 30, 2011 at 4:08 pm
(invoke-sqlcmd -serverinstance sqlserv -database playground "exec dbo.myproc" | select column1,column2 | ConvertTo-Xml -NoTypeInformation).save("c:\temp\teste.xml")
That didn't work either. But a powershell function was recently posted to tech net that will take my normal output (not xml) and transform it in PS. The logic is also used for a report so I actually like it a lot better. 🙂
http://gallery.technet.microsoft.com/scriptcenter/19e04a23-e81a-443e-a3b2-80f82d4af9b4
Barbara
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply