2
\$\begingroup\$

I am currently storing form data in an XML format:

<cfsavecontent variable="myFormData">
<cfoutput>
  <ul class="xoxo">
  <cfloop list="#form.fieldnames#" index="item">
    <cfloop list="#form[item]#" index="eachItem">
  <li><b>#xmlformat(item)#</b> <var>#xmlformat(eachItem)#</var>      
    </cfloop>
  </cfloop>
 </ul>
<cfoutput>
</cfsavecontent>

Then do a single insert:

<cfquery>
   INSERT INTO table (formData)
   VALUES (<cfqueryparam value="#myFormData#" cfsqltype="cf_sql_varchar">)
</cfquery>

When I pull the data out, I can:

<cfquery name="qryData">
   SELECT formData
   FROM table
   WHERE ID = <cfqueryparam value="#ID#" cfsqltype="cf_sql_integer">
</cfquery>

<cfoutput query="qryData">#formData#</cfoutput>

OR

<cfquery name="qryData">
   SELECT li.value('(b/text())[1]', 'varchar(50)') AS Item, 
      li.value('(var/text())[1]', 'varchar(50)')   AS Value
   FROM table
   CROSS APPLY XmlData.nodes('/ul/li') AS ul(li)
   WHERE ID = <cfqueryparam value="#ID#" cfsqltype="cf_sql_integer">
</cfquery>

<cfoutput query="qryData">#Item# #Value#</cfoutput>

Some developers have concerns about storing the data in this format. Is this the best way to store arbitrary form data?

\$\endgroup\$
  • \$\begingroup\$ How is the data used? It seems very odd to render it as an HTML list, then store it thus. \$\endgroup\$ – Adam Cameron Dec 18 '13 at 6:58
  • \$\begingroup\$ Is there a reason that you would not store the data in JSON format? It might be simpler and easier to work with the data with SerializeJSON and DeserializeJSON and the resultant structures than with XML parsing functions. \$\endgroup\$ – Brian Dec 18 '13 at 13:47
  • \$\begingroup\$ @Brian: I don't use JSON because a) There is no JSON format in SQL Server b) It can be displayed in a clean format without additional processing \$\endgroup\$ – James A Mohler Dec 18 '13 at 20:34
  • \$\begingroup\$ @Adam I am able to tap into the data having three natures at the same time:a) It is HTML so it can be displayed as is, b) It is XML so it can be treated nearly the same way as being on a more formal table structure and c) It is a string, which can easily be pushed around as a string \$\endgroup\$ – James A Mohler Dec 18 '13 at 20:36
2
\$\begingroup\$

Well, it depends what you want to do with this data. If you ever want to query individual field names or debug an issue with a field you will have a hard time extracting this kind of information out of an XML blob.

Potential pitfall: According to the coldfusion docs, CF_SQL_VARCHAR maps to varchar columns on MSSQL. You need to make sure that the column is varchar(max) as XML can get pretty verbose and any character limit might get hit.

On how to better store the data this is hard to say. From your example it looks like a form has fields and each field could have multiple values. This looks like a classic 1:n relationship to me:

Form table:

FormID | Name | Description | ...

Field Name table:

FieldID | FormID | Name

Field Value table:

ValueID | FieldID | Value
\$\endgroup\$
  • \$\begingroup\$ You touch on a lot off good points, let me address the 1:n relationship. It is a 1:n relationship, just so happens that the n part is stored on the original table. The CROSS APPLY is basically a LEFT JOIN after shredding the data. \$\endgroup\$ – James A Mohler Dec 18 '13 at 15:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.