Storing arbitrary form data in SQL as an XML blob

Question

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?

Answer 1

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