SQLCMD and use of :XML ON

[JayKusch]

Where do I place the XML: ON command in the sqlcmd string to get it to work. So far I have been fruitless.

I am trying to export out the single nVarChar(MAX) column from the table listed. It is over 8000 chars long and am told by developers it could generate a file upwards of 50MB.

SO I figured the :XML sqlcmd command may do the trick.

If someone knows of a better way, please drop that to me also.

Here is the code I just attempted:

EXEC Master..xp_CmdShell 'sqlcmd  -S(local) -E -Q":XML ON SET NOCOUNT ON SELECT * FROM MyDB.dbo.MyTable" -oC:\MyFile.txt -h-1'

Thanks

J. Kusch

 

[gmmastros]

I would recommend you use the FOR clause.

[tt][blue]EXEC Master..xp_CmdShell 'sqlcmd -S(local) -E -Q"SET NOCOUNT ON SELECT * FROM MyDB.dbo.MyTable [!]For XML Auto[/!]" -oC:\MyFile.txt -h-1'[/blue][/tt]

or

[tt][blue]EXEC Master..xp_CmdShell 'sqlcmd -S(local) -E -Q"SET NOCOUNT ON SELECT * FROM MyDB.dbo.MyTable [!]For XML Auto, Elements[/!]" -oC:\MyFile.txt -h-1'[/blue][/tt]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JayKusch]

Interesting ...

OK, a bit of history here. The column we are pulling from is set as a nVarChar(MAX).

The column is populated by another SP that dynamically builds an XML string. Some of the XML strings that are built can become very large (from our dev team, they can generate a file nearing 50MB in some cases.

Now the XML string that was dynamically build and which is contained in the nVarChar(MAX) field looks GREAT!

I am able to use sqlcmd to export it to a text file and once in the file ... it looks GREAT there too.

I used the -y0 param (width_display) w/ sqlcmd to generate the file and overcome the default 256 character limit on the file size:

EXEC Master..xp_CmdShell 'sqlcmd -S(local) -E -Q"SET NOCOUNT ON SELECT * FROM M360.dbo.tBackupAssociationAsXML" -oC:\BackupAssociationAsXML.txt -h-1 -y0'

The issue we have here is that I am reading the -y0 param is good up to a 1MB file.

The file size generated by what I currently have in the nVarChar(MAX) field was only 290KB.

Here is what I pulled from BOL:

If display_width is 0, the output is truncated at 1 MB. You can use the :XML ON command to prevent truncation of the output. The :XML ON command is described later in this topic.

So now I am back to needing to use the :XML command and then of course needing to know where it is place in the sqlcmd string.

Thanks!

Thanks

J. Kusch

 

[JayKusch]

By the way ... I did execute the SQLCMD with the params gmmastros suggested but the file created looked as if the column's data had been convert to binary or hex.

It was no longer in an XML format like before.

Thanks

J. Kusch