I have some legacy code that runs this command:
xp_cmdshell SQLXML.exe -Sserver\instance -T -F"\\spiedata\Meetings\XML Ticket Masters\Test\AE12 Meeting Tickets.xml" -Q"SummaryData.dbo.GetMeetingTicketsXML @Meeting = AE12" -O
The GetMeetingTicketsXML stored proc has a "select...xml explicit" command. The SQLXML software is installed on the current cluster. I'm moving to a new cluster and I'd prefer not to install SQLXML on the cluster; is there a better SQL2008R2 option?
asked Jul 18, 2012 at 11:01 PM in Default
Seems like you are exporting data as an xml file. So the first thing comes to mind is bcp Utility. Using the queryout option you should be able to export the results of the procedure i.e something like
answered Jul 19, 2012 at 05:48 AM
Thank you for your response. I wonder if you might help with differences I see in the output from SQLXML and BCP as regards executing the SQL 'for xml explicit'. (These files are going to a vendor so I have to replicate the SQLXML format with BCP; I've been unable to find an explanation for some of the SQLXML switches.)
The SQLXML command was: SQLXML -Sserver -T -Foutputfile.xml -Qprocname -O The BCP command suggested: BCP EXEC procname queryout filename.xml -T -N -Sserver
The SQLSML output file begins with the string The BCP output file begins with the 2 'character' string - which translates (select ascii('')) to ascii 63 and ascii NULL. This string is repeated a few times in the file. Also, the output of the BCP is Unicode (12,590 bytes) while the output of the SQLXML is UTF-8 (6346 bytes).
I'll appreciate any insight you have to offer.
answered Jul 30, 2012 at 05:21 PM