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?

• Adam Cameron: How is the data used? It seems very odd to render it as an HTML list, then store it thus.
• Brian: 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.
• James A Mohler: @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
• James A Mohler: @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

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

• James A Mohler: 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.