Wednesday, July 22, 2009

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:

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


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


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

<cfquery datasource="SMC" name="loadSMC">
Insert into aIncident(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)

This works as expected.


Anonymous said...

[url=]Communications Online Degrees[/url] [url=]Online Computer Degree Montana[/url] [url=]Online Certified College Degree[/url] [url=]Online Application University[/url] [url=]Online Degree Programs Universities[/url]

Anonymous said...

[url=]Should Online Schools Be Allowed[/url] [url=]High School Online Diploma Test[/url] [url=]Online Schools In Illinois[/url] [url=]Administrative Evaluation Of Online Courses[/url] [url=]Sabbath School Lesson Online[/url]