x

SQLXML replacement

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?

more ▼

asked Jul 18, 2012 at 11:01 PM in Default

avatar image

LightheartAl
12 4 4 4

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

 EXEC xp_cmdshell 'bcp "EXEC SummaryData.dbo.GetMeetingTicketsXML @Meeting = AE12" queryout "\\spiedata\Meetings\XML Ticket Masters\Test\AE12 Meeting Tickets.xml" -N -T'

more ▼

answered Jul 19, 2012 at 05:48 AM

avatar image

Usman Butt
14k 6 13 21

(comments are locked)
10|1200 characters needed characters left

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.

Thanks, Al

more ▼

answered Jul 30, 2012 at 05:21 PM

avatar image

LightheartAl
12 4 4 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x4

asked: Jul 18, 2012 at 11:01 PM

Seen: 738 times

Last Updated: Jul 30, 2012 at 05:21 PM

Copyright 2016 Redgate Software. Privacy Policy