> 1) Using wddx is, well, a really bad idea. How can you > describe a product using wddx?
_VERY_ easily... <cfset product = structnew()> <cfset product.productname = "my product"> <cfset product.price = 20.00> <cfset product.description = "blah blah blah"> <cfwddx action="cfml2wddx" input="#product#" output="xml"> <cfdump var="#xmlparse(xml)#"> Even if you deserialize it with xmlparse, you still get a plenty legible data structure, although within ColdFusion the results of deserializing with wddx are more accessible than those produced by xmlparse(). Not that I would store product information this way anyhow, but just to give you an idea. > 2) Storing XML in a text field is, well, a bad idea. > Besides the obvious performance implications, you're > already running into problems with having multiple > pieces of data glommed into a big field. That's not _necessarily_ true. If you do a good job with the cacheing, you _can_ get better performance results from storing xml than from accessing the db at run-time on each request. Right tool for every job tho, and of course, xml isn't always the right tool. > Seems to me that you've actually lost functionality going > the way you're going. > I would at least create objects that represent a data > layer that you can use > for CRUD operations to the database. It would also make > sense to create > objects that represent things like "product" that use the > data layer for > attribute filling and functionality. Your database would > be set up in a > proper normalized fashion. If you really do have 40 > fields for products, > consider going all the way up to 5th normal form in your > DB design. Given what he's described of the application, I would tend to agree. >> >> But now I ran into a problem. What if I want to search by >> model? Say I > want >> all the items in the database that are model "XYZ". Well >> the only option I >> have it to search the xml text field for XYZ, but then I >> "could" get > double >> the number of fields I want if any other xml data has >> "XYZ" in it. >> >> >> >> Does anyone know of a good solution to this? Is there a >> way to search the >> specific xml fields while its still in the database? I've >> looked into the >> Microsoft OpenXML, but that seems overly complex. >> >> >> Any ideas would be great. Unfortunately WDDX isn't a great format to perform this kind of search (although another xml format might not be so bad)... For example: <VAR NAME='PRODUCT'><STRUCT> <VAR NAME='PRODUCTNAME'><STRING>my product</STRING></VAR> <VAR NAME='PRICE'><STRING>20.00</STRING></VAR> <VAR NAME='DESCRIPTION'><STRING>blah blah</STRING></VAR> </STRUCT></VAR> This is what your wddx packet looks like. In normal SQL you could search for '<VAR NAME=''PRODUCTNAME''><STRING>%xyz%</STRING>' but unfortunately because there will likely be other <STRING> and </STRING> elements after the one you're looking for, this won't eliminate the remaining fields from the search. As opposed to another xml format such as: <product> <name>my product</name> <price>20.00</price> <description>blah blah</description> </product> Now if each record has its own xml packet, you could easily search by name by using '<name>%xyz%</name>' as the search string... Somewhere out there exists a method of using regular expressions in SQL Server although afaik I've only seen it as a stored procedure. If there's a way to implement that as a function, you might be able to implement that to perform your searches even on the wddx packet, however, it will (no question) degrade performance for the search. s. isaac dealey 972-490-6624 team macromedia volunteer http://www.macromedia.com/go/team chief architect, tapestry cms http://products.turnkey.to onTap is open source http://www.turnkey.to/ontap ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Get the mailserver that powers this list at http://www.coolfusion.com
