How to export more than 1MB in XML format using sqlcmd and without an input file?

Question

In SQL Server 2008, I want to export the result of a stored procedure to a file using sqlcmd utility. Now the end of my stored procedure is a select statement with a "for xml path.." clause at the end.

I read on BOL that if I don't want my output truncated when reaching 1MB file size, I have to use this :XML ON command, but it should be placed on its own line, before calling the stored procedure.

Does any of you experts know if it is possible to do that without specifying an input file for sqlcmd? (I'm calling sqlcmd like this:

exec master..xp_cmdshell 'sqlcmd -Q"exec storedProcedureName @param1=value1, @param2=value2" -o c:\exportResults.xml -h-1 -E',

but "storedProcedureName" and its parameters can change, which would mean 1 input file per passed parameters to sqlcmd)

Also, it seems that I can't use bcp instead of sqlcmd because my stored procedure is creating a temporary table and performing DML statements on it?

Thanks a lot

jon · Accepted Answer · 2010-03-30 12:07:44Z

0

I answer my question (using powershell):

http://ask.sqlservercentral.com/questions/5322/is-it-possible-to-use-the-xml-on-option-without-using-an-input-file/5380#5380

answered Mar 30, 2010 at 12:07

jon

4086 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jon Over a year ago

here it is

Pasi Savolainen · Accepted Answer · 2018-04-26 10:35:38Z

0

If you can use powershell as your executing shell, then you can have the :XML ON in the -Q parameter as well, just encode endline character \\n (`n in powershell)

sqlcmd -Q":XML ON\`n exec storedProcedureName @param1=value1, @param2=value2" -o c:\exportResults.xml -h-1 -E

answered Apr 26, 2018 at 10:35

Pasi Savolainen

2,5301 gold badge23 silver badges35 bronze badges

Collectives™ on Stack Overflow

How to export more than 1MB in XML format using sqlcmd and without an input file?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related