I was under the impression that if you declared the appropriate character set (utf-8 in most cases I believe, though I'll admit to not being an authority on such matters) you only needed to escape those 5 characters (&'"<>) in order to meet the requirements of "well formed xml". Is this not the case? And if not, would it potentially be possible to use <![cdata[]]> I'm lead to believe this is another means of escaping character data in xml. All your otherwise unescaped data would go in the inner pair of brackets [].
> OK, I think I've got a working prototype, but there is a > wrinkle... You recommended a UDF in SQL to escape out the > characters illegal in XML. However, we have many > diacritical marks and characters which would be quite > difficult to include in the UDF. The CFML XMLFormat() > function does do this. But adding a line to run the > results through this function causes all the the tag marks > to change. Then running these results through some more > refinements effectively lengthens the processing time > tremendously. > You are right in that SQL does do the encoding much, much > faster than CFML. So I will be re-investigating the other > XML functions of SQL Server. In the meantime, does anybody > have a good SQL XMLFormat() UDF they'd be willing to > share? > Thanks, again. > 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
