Using ColdFusion to add XML data to a SQL database

Using the ColdFusion XML document object can present some unexpected challenges when updating databases. Variable references to elements in this structure do not return values as one might expect but instead return new structures. Useful ColdFusion functions such as <cfoutput> resolve these new structures into values behind the scene, which can mislead you when trying to debug wrong values being placed into the database.

As an example, consider the following. An xml file has a field named ICN, among others. We will read the file and update a SQL database. First, we get a file handle.

< cffile variable="gmrXML" file=" “wits.xml" action="read">

Then a ColdFusion XML Document Object datatype, using our file handle and the ColdFusion xmlParse() function:

< cfset myxml=" xmlParse(gmrXML)">

When we use cfoutput to see what we have, all is well:

<cfoutput>
<cfloop to="#arrayLen(myXML.incidentList.Incident)#" from="1" index="i">

#myXml.incidentlist.incident[i].ICN#
…

The output on our Web page shows the ICN value we expect:

200458431

Good enough. We now add it to our database with the following code:

<cfquery datasource="SMC" name="loadSMC">
Insert into aIncident(ICN)
values(#myXml.incidentlist.incident[i].ICN#
< /cfquery>

and when we look, the value in the ICN column in the database is not 200458431 but instead is

<?xml version="1.0"?>

This disappointing result is because in the cfquery above, we were treating the ICN reference as a value. cfouput helped in this deception because we could treat it as a value with this sophisticated function. A look at cfdump shows what happened.

Here is a dump of the reference:

It is not a value but a structure. To get to the value we want, we need to add .xmlText to the end of the reference we used so that the query now looks like:

<cfquery datasource="SMC" name="loadSMC">
Insert into aIncident(ICN)
values(#myXml.incidentlist.incident[i].ICN.xmlText#
</cfquery>

This works as expected.

Fan Traps and Chasm Traps

When consolidating numerous data sources, including databases and lists, care must be taken to issues besides finding proper keys or, alternatively, match codes. That is pretty daunting on its own. Worse comes, Chasm traps and Fan traps. They can mislead us into very wrongful and wasteful action in our direct marketing even if we have organized the database properly.

In many consolidated, operational data stores, we typically end up with a core record in a “fact” table that is associated with multiple “dimension” tables with characteristics for each record in the fact table. For example, a university Alumni table as our “fact”/core table, a Donations table and a Financial Aid table, among others that tell us more about the person in the fact table.

Someone in the Alumni table may have made 0, 1 or many donations and so have 0 to many associated records in the Donations table. Likewise, someone in the Alumni table may have received 0, 1 or many scholarships or loans to attend college. Many of our data associations in this type of consolidated customer database will have this 1 to many or 1-M quality.





This can result in very subtle errors and very misleading interpretations in the analysis we do on the database or in the more mundane operations such as mailings. Let’s say we want to test the hypothesis that Alumni who received financial aid are more likely to donate money than those who did not, a propensity to donate. The correct way to accomplish this is to run two queries, one a list of alumni and the sum of all amounts they donated, and the other a list of alumni and the sum of all financial aid they received. Then we merge the two results sets.

It is tempting instead to run one query that reads our alumni table and simultaneously sums both donations and financial aid in the related tables. In a SQL database we have just stepped into a chasm trap. Such a query will disproportionally count both the donations and the financial aid for those friends who have made more than 1 donation AND received more than 1 loans or scholarships. (see Business Intelligence Blog, p 1 or IDS, pp 1-3 or http://db.grussell.org/resources/pdf/co22001%20notes.pdf

References
International Documentation Solution (April 14, 2000). Recognizing and resolving Chasm and Fan traps when designing. Retrieved on August 28, 2008 from http://www.eagle.co.nz/businessobjects/pdfs/ttchasm.pdf

Napier University (August 2002). Database SystemsStudent Notes. Retrieved on August 28, 2008 from http://db.grussell.org/resources/pdf/co22001%20notes.pdf