I'm having difficulty exporting XML from SQL Server 2000.
I have the select statement written. It's not finished, but it is generating XML. In order to tune the structure of the select statement I need to get the data into a browser or text file. When SQL generates XML it returns blocks of 8192 characters in a universal table. This is terrible to read and doesn't have a root element.
My problem is that every time I try to export the data it either doesn't work, or returns an error.
I was going to do this with a DTS package but the wizard chokes when it sees the For XML clause. It returns an error that states XML is prohibited in DTS packages or something very similar to that.
I tried hiding the XML in a Stored Procedure, same message.
I also tried using an ASP page to fetch the contents into a variable that I could manipulate. The ASP stream object seems very complicated and it's cumbersome to schedule ASP pages to run. I also don't like the security repurcussions of having a login somewhere to run a piece of code in IE.
The desired solution is a DTS package that will generate the XML and export it to a file. The file then can be touched up (add a header and footer) and moved to the appropriate directory for processing. I'm working on the first file of 6 that belong to a particular vendor's feed. These files will be generated at least once per day and sent to the vendor/partner to keep their system in synch. Thus a scheduled DTS package that outputs a file is a manageable and automatable solution.
The tactic I'm trying now is storing the XML into a table and then exporting the contents of the table. I'm having a really hard time putting a handle on the data from within SQL. I've also ruled out Xpath because the DB resides on a production web server and that raises an unacceptable risk.
I'm open to other ideas as long as they conform to using a DTS package and a SQL centric solution. Microsoft's documentation suggests that this is possible, but everything I can find now points to .net. The books online says this can be done, but I can't get it to work. I'm hoping a fellow EE member can help me see the light in time to enjoy a Xmas break. :)
