April 10, 2003 at 3:45 am
Annoying problem. I need to create an XML document from a query result set. I am currently using the following query
Declare @outputfile varchar(255), @query varchar(255), @templatefile varchar(255), @tmpFile varchar(255)
select @tmpFile = '(PATH TO TEMPLATE)\template.tpl'
select @outputfile = '(PATH FOR OUTPUT)\' + convert(varchar, @reportid) + convert(varchar, @diagramid) +
'_' + convert(varchar(12), getdate(), 5) + '.xml'
select @query = 'execute usp_select_report_xml '+ convert(varchar(10), @reportid) + ',' + convert(varchar(10), @diagramid)
exec sp_makewebtask @outputfile, @query, @templatefile = @tmpFile
In the stored procedure 'usp_select_report_xml' that is executed I am using the 'FOR XML AUTO, ELEMENTS' clause.
This whole thing works perfectly provided that the XML string created DOES NOT exceed 2033 characters. If the string is longer than 2033 characters then it is split into multiple rows. This causes the XML document that gets created to fail parsing because the XML text is broken up. Manually editing the XML file and rejoining the text corrects this however that defeats the whole point of what I am trying to do.
Now the main point of this problem is this, I know that query analyzer uses ODBC and I know that ODBC DOES NOT handle the 'FOR XML' clause hence the 2033 character maximum row size. If I use an OLEDB datasource everything is fine.
Now the second part of the problem, I am using Coldfusion MX server. This version of Coldfusion Server NO LONGER supports OLEDB datasource connections, everything has to now go through JDBC.
Does anyone know of a workaround for using the 'FOR XML' clause via an ODBC datasource or have any clever ideas on how to manipulate the XML string before writing out the XML file?
As stated, I am using Coldfusion MX server and SQL2000. This is not really a major issue as I do have another, slightly more long winded way of acheiving the desired result but this is just bugging me.
Any help will be much appreciated.
April 14, 2003 at 8:00 am
This was removed by the editor as SPAM
April 14, 2003 at 8:30 am
There's a trace flag that will pretty up the output in Query Analyzer - 257.
Andy
April 14, 2003 at 8:54 am
I am unsure of the ColdFusion analogy, but in ADO with COM you would use the ADO.Stream to stream the xml into a complete stream. That sounds like what you want to do, sorry I can't help with the CF...
Tim C //Will code for food
Tim C //Will code for food
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply