Hi there,
If you are still wishing to go down the route of XML after the previous posts, you
might wish to externatize some specific nodes in your XML data. That way the value of
particular nodes and particular atttributes are stored seperate from the XML in
another table with the ID of the XML record they belong to.
I don't know if this makes sense in your situation, but if you had a XML doc like so
in a text field of your DB:
<product>
<name id="123">My product</name>
<sku>AABBCC123</sku>
</product>
Related to these prodocut XMLs you have a reference field(s) for e.g.
//product/name and/or
//product/sku
or whatever, it just has to be valid Xpath syntax.
Now when you go to save/update the XML text field in your DB you read these reference
values, simply query the XML object using the Xpath and store the value somewhere (say
in a 'references' table) with the record ID of the XML text field.
Now if you wish to look up all Products with a name of 'My product', you just need to
lookup the references table, and this ought to give you a list of the DocIDs where the
XML node 'Products' has the name 'My product'. You can then read in and create an XML
object or whatever. It's like a book's index.
This of course is only useful if you have a number of XML fields that you might like
to search by etc. However, I have this implemented at our place, and it's fast and
effective for our uses.
Hope it makes sense or helps :)
Ciaran
>Somewhat OT but this is for a CF app and I thought it could be useful for
>others too.
>
>I'm in the processing of making a very flexible OO inventory app. The basic
>rundown is this.
>
>
>
>You have a table in the DB called "Products". This is where you inventory
>goes. Now because this database is going to be used by several different
>e-commerce apps, it has to be flexible. App 1 may have a field called
>"MODEL" for the item data to go into the table.
>
>App2 might have a field called "YEAR", and etc. Now if I make new columns
>for every app, I'm going to end up with a products table with like 40
>columns. Sure that will work, but its not very organized, and there are
>things like, one product cant have more than one model, and etc.
>
>
>
>Instead I want to store the data as XML in a text field. Pretty simple
>right?
>
>I format the data using cfwddx. This way I can easily format the data as it
>comes in and out of the database. It works great.
>
>
>
>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.
>
>
>
>Thanks,
>
>-Alan
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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
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
