XML vs. SQL with large amounts of text
Ok, this may be a stupid question, but when choosing a data storage method (XML vs. SQL) what is the best method to store large amounts of text? I've got a scenario where I will have 30,000+ Businesses in a database and each Business will have at least one report of considerable length. I'm currently storing every report as an XML document with a naming convention that is the same as the business ID. I considered using SQL to store each report as a glob of XML, but the XML documents may exceed the 8000 character limit of a SQL field. Is there something larger than that field or is it more advisable to use XML documents for each report? Each XML Document at their smallest will be appx 6-8k, at their largest will be < 30k. Is it advisable to stuff the XML contents into a SQL table instead as storing as XML files? Any insight appreciated. Thanks, Joshua Miller __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: XML vs. SQL with large amounts of text
Resurrecting an old thread...
On Thursday, Aug 15, 2002, at 21:41 US/Pacific, Matt Liotta wrote:
> http://www.devx.com/xml/articles/ml062502/ml062502-1.asp. The article
Interesting article. I've just started looking at Oracle's native XML
support in 9i (release 9.2). It looks very promising... a way to get
the best of both worlds. You can return XML from a standard SQL query
on a relational table, you can mix relational and XML data in a record,
you can aggregate records into a single XML document etc. You can see
some syntax examples here:
http://otn.oracle.com/tech/xml/xmldb/htdocs/sql_xml_codeexamples.html
e.g.,
select xmlelement("emp",
xmlattributes(e.job || ' ' || e.ename as "name"),
xmlelement("department", e.deptno),
xmlelement("depts", (SELECT COUNT(*)
FROM scott.dept d
where d.deptno = e.deptno))) as result
from scott.emp e;
You will need to register with Oracle TechNet to view this - worth
doing, it's free and there's a lot of useful information there
(including online docs with full SQL specs).
I'll be installing 9.2 shortly and having a play around with this -
I'll report back what I think.
An Architect's View -- http://www.corfield.org/blog/
Macromedia DevCon 2002, October 27-30, Orlando, Florida
Architecting a New Internet Experience
Register today at http://www.macromedia.com/go/devcon2002
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
RE: XML vs. SQL with large amounts of text
> Interesting article. I've just started looking at Oracle's > native XML support in 9i (release 9.2). It looks very > promising... a way to get the best of both worlds. You can > return XML from a standard SQL query on a relational table, > you can mix relational and XML data in a record, you can > aggregate records into a single XML document etc. It's worth noting that you can do some of this in SQL Server 2000, using the OPENXML function and XPath queries to work with XML data, and using FOR XML to return XML data. Of course, you won't be able to run that on your TiBook without VPC. > You will need to register with Oracle TechNet to view > this - worth doing, it's free and there's a lot of useful > information there (including online docs with full SQL > specs). Not to mention actual limited-license copies of Oracle database server software. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: XML vs. SQL with large amounts of text
On Sunday, October 6, 2002, at 06:46 PM, Dave Watts wrote: > It's worth noting that you can do some of this in SQL Server 2000, > using the > OPENXML function and XPath queries to work with XML data, and using > FOR XML > to return XML data. Of course, you won't be able to run that on your > TiBook > without VPC. > > But you should be able to run it on your TiBook *with* VPC or on a Linux box *with* VMWare. Dick "I love deadlines. I like the whooshing sound they make as they fly by." - Douglas Adams - ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: XML vs. SQL with large amounts of text
:> Ok, this may be a stupid question, but when choosing a data storage :> method (XML vs. SQL) what is the best method to store large amounts of :> text? Why not a combination of both? "Normal" database type stuff - business name, address, blah, blah in your SQL database, with a field for the file names of the XML reports? That way, from a simple SQL query you could call up links to the reports, without having to store the reports in the database itself or going over the 8000 character limit... __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: XML vs. SQL with large amounts of text
Have a look at Xindice -- an XML database: http://xml.apache.org/xindice/ I understand that both Matt Liotta and Tom Dyson have interfaced Xindice with CFMX. Dick On Thursday, August 15, 2002, at 08:24 PM, Joshua Miller wrote: > Ok, this may be a stupid question, but when choosing a data storage > method (XML vs. SQL) what is the best method to store large amounts of > text? > > I've got a scenario where I will have 30,000+ Businesses in a database > and each Business will have at least one report of considerable length. > I'm currently storing every report as an XML document with a naming > convention that is the same as the business ID. I considered using SQL > to store each report as a glob of XML, but the XML documents may exceed > the 8000 character limit of a SQL field. Is there something larger than > that field or is it more advisable to use XML documents for each report? > > Each XML Document at their smallest will be appx 6-8k, at their largest > will be < 30k. Is it advisable to stuff the XML contents into a SQL > table instead as storing as XML files? > > Any insight appreciated. > > Thanks, > > Joshua Miller > > __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: XML vs. SQL with large amounts of text
You would like need to use a CLOB field instead of a varchar in this case. There is no limit to the amount of data you can store in a CLOB field. Depending on what database you are using, the field data type may be called something else e.g. text with SQL Server. Further, Oracle supports a field data type of BFile, which is a pointer to actual file system file. Matt Liotta President & CEO Montara Software, Inc. http://www.montarasoftware.com/ V: 415-577-8070 F: 415-341-8906 P: [EMAIL PROTECTED] > -Original Message- > From: Joshua Miller [mailto:[EMAIL PROTECTED]] > Sent: Thursday, August 15, 2002 8:24 PM > To: CF-Talk > Subject: XML vs. SQL with large amounts of text > > Ok, this may be a stupid question, but when choosing a data storage > method (XML vs. SQL) what is the best method to store large amounts of > text? > > I've got a scenario where I will have 30,000+ Businesses in a database > and each Business will have at least one report of considerable length. > I'm currently storing every report as an XML document with a naming > convention that is the same as the business ID. I considered using SQL > to store each report as a glob of XML, but the XML documents may exceed > the 8000 character limit of a SQL field. Is there something larger than > that field or is it more advisable to use XML documents for each report? > > Each XML Document at their smallest will be appx 6-8k, at their largest > will be < 30k. Is it advisable to stuff the XML contents into a SQL > table instead as storing as XML files? > > Any insight appreciated. > > Thanks, > > Joshua Miller > > __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: XML vs. SQL with large amounts of text
Here is the article I wrote on the subject, http://www.devx.com/xml/articles/ml062502/ml062502-1.asp. The article does include some reasons why you would want to use an XML database of an RDBMS for XML storage. Matt Liotta President & CEO Montara Software, Inc. http://www.montarasoftware.com/ V: 415-577-8070 F: 415-341-8906 P: [EMAIL PROTECTED] > -Original Message- > From: Dick Applebaum [mailto:[EMAIL PROTECTED]] > Sent: Thursday, August 15, 2002 9:33 PM > To: CF-Talk > Subject: Re: XML vs. SQL with large amounts of text > > Have a look at Xindice -- an XML database: > > http://xml.apache.org/xindice/ > > I understand that both Matt Liotta and Tom Dyson have interfaced Xindice > with CFMX. > > Dick > > > On Thursday, August 15, 2002, at 08:24 PM, Joshua Miller wrote: > > > Ok, this may be a stupid question, but when choosing a data storage > > method (XML vs. SQL) what is the best method to store large amounts of > > text? > > > > I've got a scenario where I will have 30,000+ Businesses in a database > > and each Business will have at least one report of considerable length. > > I'm currently storing every report as an XML document with a naming > > convention that is the same as the business ID. I considered using SQL > > to store each report as a glob of XML, but the XML documents may exceed > > the 8000 character limit of a SQL field. Is there something larger than > > that field or is it more advisable to use XML documents for each report? > > > > Each XML Document at their smallest will be appx 6-8k, at their largest > > will be < 30k. Is it advisable to stuff the XML contents into a SQL > > table instead as storing as XML files? > > > > Any insight appreciated. > > > > Thanks, > > > > Joshua Miller > > > > > __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
