This works fairly well. Right now, besides the unicode problem, another problem is
the length of the field. Apparently there is a maximum of 240 characters for the max
length of the string. I am not the CF Sys Admin...is there any way I could over-ride
this setting?
Thanks.
Karen
>>> [EMAIL PROTECTED] 7/24/2003 12:34:21 PM >>>
<cfsavecontent> is CF5 ... there's a CF4.5 compatible alternative which is a custom
tag ... there's a copy of it in the core files for FB3, otherwise it looks something
like this:
<cfparam name="attributes.variable" type="string">
<cfif thistag.executionmode is "end">
<cfset "#attributes.variable#" = thistag.generatedcontent>
<cfset thistag.generatedcontent = "">
</cfif>
Then instead of cfsavecontent you can use this (I forgot the cfoutput tags before)...
<cfoutput>
<cf_savecontent variable="xmlstring">
<?xml version="1.0" charset="utf-8"?>
<departments>
#arraytolist(rs["xmlinformation"],"")#
</departments>
</cf_savecontent>
</cfoutput>
This should produce roughly the same results as cfsavecontent although it might be
(unnoticeably in most cases) a tad slower.
hth
> Is <cfsavecontent> MX? We only have CF4.5. But we do have
> SQL 2000!
> Thanks.
> Karen
>>>> [EMAIL PROTECTED] 7/24/2003 9:23:58 AM >>>
> Even without using FOR XML you can output directly to XML
> from SQL Server... We had a process where I'm working
> currently which had been taking 3 hours to generate xml
> content to send to another outsourcing company.
> The reason is this: In the beginning as you're building
> the first couple thousand records of xml content, CF is
> pretty quick about it, generating each record at under a
> second. As the xml string becomes longer and longer, it
> takes cf longer and longer to find the end of the string
> to append the next record. (iirc there's a similar issue
> with cffile action="append")... So once you've processed
> over 10k records it's solidly taking over 1 second per
> record and still slowing...
> So what'd we do? Generate xml with a view...
> For example:
> CREATE VIEW dbo.v_xml_departments AS
> SELECT '<department departmentid="' + dbo.xmlformat() +
> '">'
> + CASE LEN(TRIM(ISNULL(othercolumn,''))) WHEN 0 THEN ''
> ELSE '<otherelement>' + dbo.xmlformat(othercolumn)
> + '</otherelement>' END + '</department>'
> AS xmlinformation
> FROM ourtable
> dbo.xmlformat is a SQL2k user defined function which does
> this
> set xml = replace(xml,'&','&');
> set xml = replace(xml,'''',''');
> set xml = replace(xml,'"','"');
> set xml = replace(xml,'<','<');
> set xml = replace(xml,'>','>');
> return xml;
> this method gives us complete control over the xml
> returned by the view, then we use cf to turn it into a
> complete xml packet
> <cfquery name="rs" datasource="ourdsn">
> SELECT * FROM v_xml_departments
> </cfquery>
> <cfsavecontent variable="xmlpacket">
> <?xml version="1.0" charset="utf-8"?>
> <departments>
> #arraytolist(rs["xmlinformation"],"")#
> </departments>
> </cfsavecontent>
> With 65k records in a reasonably complex schema, this
> method reduced processing time to generate the xml from
> 3hrs to 10 minutes. :) The query takes about 1 minute, the
> xml generation (the arraytolist function call) takes about
> 9 minutes.
> ymmv & hth :)
> s. isaac dealey 972-490-6624
> new epoch http://www.turnkey.to
> lead architect, tapestry cms http://products.turnkey.to
> tapestry api is opensource http://www.turnkey.to/tapi
> team macromedia volunteer
> http://www.macromedia.com/go/team
> certified advanced coldfusion 5 developer
> http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
>> Did you know you can make SQL return a recordset in XML
>> format? Simply
>> append 'FOR XML' statement at the end of your SQL select
>> statement and
>> the result is an XML document. The 'FOR XML' statement
>> has 3 modes:
>> AUTO, RAW, and EXPLICT. There's also stiplulations on
>> what type of SQL
>> Select statement you can use 'FOR XML'. I would check
>> SQL
>> Server Books
>> and search for 'FOR XML clause'. This is for SQL Server
>> 2000. Not sure
>> if SQL Server 7 supports 'FOR XML'
>> HTH - Tom
>> -----Original Message-----
>> From: Karen Harker
>> [mailto:[EMAIL PROTECTED]
>> Sent: Thursday, July 24, 2003 8:58 AM
>> To: [EMAIL PROTECTED]
>> Subject: Processing time question
>> I'm working on a program that effectively exports data
>> from SQL Server
>> into an XML file. All I've done is simply hard-code the
>> tags, and
>> plugged in values based on the appropriate database
>> columns. This data
>> is set as a variable and then the variable is saved as a
>> file on the
>> server. Eventually, I would like to FTP that file to
>> another server.
>> My problem is file size and processing time. I've
>> estimated that there
>> will be about 9MB of data. I've been successful in
>> outputting the data
>> into multiple files of up to 100 records each. This
>> typically did not
>> take very long to process (15-30 seconds). In the end,
>> however, we
>> would like to have the data in one large file. When I try
>> to process the
>> entire dataset all at once, it takes much too long (I've
>> timed out at
>> 1000 seconds!). I've tested it at processing 1000
>> records
>> (there are
>> approx 4800 records) and I was able to get 3 sets loaded
>> before timing
>> out at 500 seconds. So it seems that the processing time
>> rises almost
>> exponentially with the number of records per file.
>> My questions are:
>> 1) Why is that?
>> and
>> 2) What can we do?
>> We would like to get this exporting/transfer/importing
>> automated as much
>> as possible because the data will have to be refreshed at
>> least once a
>> day, if not more frequently. Also, importing the
>> multiple
>> files could
>> cause problems with the target resource.
>> Thanks.
>> Karen R. Harker, MLS
>> UT Southwestern Medical Library
>> 5323 Harry Hines Blvd.
>> Dallas, TX 75390-9049
>> 214-648-8946
>> http://www.utsouthwestern.edu/library/
>> -----------------------------------------------
>> To post, send email to [EMAIL PROTECTED]
>> To unsubscribe:
>> Send UNSUBSCRIBE to [EMAIL PROTECTED]
>> To subscribe / unsubscribe: http://www.dfwcfug.org
>> -----------------------------------------------
>> To post, send email to [EMAIL PROTECTED]
>> To unsubscribe:
>> Send UNSUBSCRIBE to [EMAIL PROTECTED]
>> To subscribe / unsubscribe: http://www.dfwcfug.org
> -----------------------------------------------
> To post, send email to [EMAIL PROTECTED]
> To unsubscribe:
> Send UNSUBSCRIBE to [EMAIL PROTECTED]
> To subscribe / unsubscribe: http://www.dfwcfug.org
> -----------------------------------------------
> To post, send email to [EMAIL PROTECTED]
> To unsubscribe:
> Send UNSUBSCRIBE to [EMAIL PROTECTED]
> To subscribe / unsubscribe: http://www.dfwcfug.org
s. isaac dealey 972-490-6624
new epoch http://www.turnkey.to
lead architect, tapestry cms http://products.turnkey.to
tapestry api is opensource http://www.turnkey.to/tapi
team macromedia volunteer http://www.macromedia.com/go/team
certified advanced coldfusion 5 developer
http://www.macromedia.com/v1/handlers/index.cfm?ID=21816
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To unsubscribe:
Send UNSUBSCRIBE to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To unsubscribe:
Send UNSUBSCRIBE to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
