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.