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

LightheartAl gravatar 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

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(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

LightheartAl gravatar 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 574 times

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