SQL Server returned column limit: osql utility

I am using a statement similar to this in an attempt to retrieve XML to a file:

EXEC master..xp_cmdshell 'osql -h-1 -w10000 -S ben -U todd -P todd -q "export_needs_xml" -o c:\test.xml'

In the stored procedure I am using XML EXPLICIT to create the XML. The problem is that the record returned is broken up into multiple rows, each roughly 2037 bytes.

When the data is written to an XML file, the XML is invalid because of the splits.

Is there a way that this can be modified???
LVL 1
tpattenAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
KatanaCSConnect With a Mentor Commented:
Instead of using OSQL from xp_cmdshell, you may want to have the query write the XML to a table (global temp [##] is ok) that has a field with a TEXT datatype.  From there, you can use xp_cmdshell to BCP OUT the XML stored in the table to your XML file.

Example:
Create Table ##XmlOut (XMLText TEXT)
Insert Into ##XmlOut EXEC export_needs_xml

Exec master.dbo.xp_cmdshell 'bcp "Select XMLText From ' + db_name() + '..##XMLOut" queryout c:\test.xml -c -S' + @@ServerName + ' -T'


Regards,
KatanaCS
0
 
CleanupPingCommented:
tpatten:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
All Courses

From novice to tech pro — start learning today.