Storing WDDX or XML Data

[cf_matt]

At the moment I am storing results from form data in a wddx packet in a mediumtext field.

It all works fine but i'm sure there must be a cleaner smarter solution. I have had to enable CLOBS on the datasource recently.
It doesnt seem the best way of storing the information.

Does anybody know of a nicer solution, I am using mySQL but i would be quite happy to move it over to MSSQL if I need to.
I have a feeling MSSQL has some sort of XML storage functionality.

I don't really like the idea of writing to a file, it seems far to 80's and i should imagine it gets harder and harder for coldfusion to retrieve the file, the larger it gets.

Hope you can help

Regards

Matt

[Guest]

Matt,

We have been using WDDX to store form data in the data base for a long time. I would say that storing it in XML is a better route because most databases (SQL Server and Oracle is what I have the most experience with) do have the capability to rip through XML using X-path, etc. The best way though, that we have found out is to have a "Properties" table. So it has a "PropertyName" column, a "PropertyValue" column, a unique ID, and a Foreign Key ID to say a "Report" table that is storing the form (config) data for. So then you would query the "Properties" table, and get all the needed values from it, and then do whatever it is you need to do in your code based on those values.

Having a "Properties" table leverages itself way better to reporting on the properties, where you can write a query to get that information instead of having CF to dump that information.

Hope this helps.

Johann

[cf_matt]

Thanks Johann
I think I understand so you store the name and value pairs for each form element in a seperate table?
What do you do when an XML document has child nodes?
I still wish there was a nice solution, something that just you passed an XML packet to and you could query it like you do a physical db table.
Matt

[Guest]

Yup, basically that table is Key Value pairs for in your case the form variables. So maybe like a key of "Name" and a value of "Jon".

About the XML. In T-SQL (we use SQL Server) there is a way using X-Path to rip through any format of XML. From parent nodes to children, and even attributes within the nodes can be searched and used. For more info check out this link and Method 4: http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm .

I agree that it would be nice to be able to query XML like we can a database. I know that SQL Server and Oracle (and other DBMS as well) can return XML from a query. That might be something for you to look into also.

Hope this helps.

Johann

[Newsgroup_User]

We have been using WDDX to store form data in the data base for a long
time. I would say that storing it in XML is a better route because most
databases.

I would just like to add, in case this is not clear, WDDX IS XML. If
you look at the WDDX packet it will be in XML format.