Output ColdFusion Query Results to XML
I don’t claim to be a ColdFusion expert by any stretch of the imagination, but recently I found myself in need of coming up with a way to output query results to an XML file. My example below is based on an absolutely hideous article I stumbled across on the ColdFusion Developer Center. The example they provide has several errors in syntax: unclosed tags, unclosed quotes, unOPENED pounds, etc. It also only outputs one record!
After about an hour of playing with it I was able to get the code working properly and I thought I’d share a very basic example. The following code queries a database for names and phone numbers then outputs them all to a file name phonelist.xml in the current path:
<cfquery name="qGetPhoneList" datasource="datasource">
select first_name,last_name,phone from employees
</cfquery><!—Create a temporary variable "tempxml" to hold our XML document—>
<cfset tempxml = "<?xml version =""1.0""?>
<!DOCTYPE phonelist [
<!ELEMENT phonelist (firstname,lastname,phone)>
]>
<phonelist>">
<cfloop query="qGetPhoneList">
<cfset tempxml = "#tempxml#
<cid>#Trim("#XMLFormat(first_name)#")#</cid>
<s>#Trim("#XMLFormat(last_name)#")#</s>
<r>#Trim("#XMLFormat(phone)#")#</r>
">
</cfloop>
<cfset tempxml = "
#tempxml#
</phonelist>"><!—show the XML in the HTML output —>
<P>This is a simple XML document that’s been generated by the ColdFusion code. </P>
<cfoutput>
<xmp>
#tempxml#
</xmp>
</cfoutput><!—write out the XML—>
<cfset writePath=#ExpandPath("phonelist.xml")#>
<cfoutput>
#writePath#<br />
<cffile action = "Write" file="#writePath#" output="#tempxml#">
</cfoutput>
-rG
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Thank you! You saved me the agony of working through the bad examples. I had your code working on my db in minutes.
Thank you for solving the one line issue using the cfloop tag. I modified it a little for my needs.
This is too sweet … I’m doing a project with a bind to a cfselect and creating the xml file for 80,000 records was presenting quite a challenge … Thanks!
You are a life saver. God Bless.
Excellent example! Thank you!
I am often amazed at the power of the Internet. I was able to put Coldfusion query xml into Google and came across your article. No searching through library books, friends minds, or hours and hours of try and fail. Thank you for your personal time and effort in finding this and, more importantly, putting it out here for the rest of us.
I get the below error when using your structure above:
Only one top level element is allowed in an XML document. Error processing resource
Please help… I’m not sure what I am doing wrong.
<cfset tempxml = ”
<!DOCTYPE courses[]>
“>
select * from course where courseid = 23
<cfset tempxml = “#tempxml#
#Trim(“#XMLFormat(courseid)#”)#
#Trim(“#XMLFormat(university)#”)#
#Trim(“#XMLFormat(semester)#”)#
#Trim(“#XMLFormat(courseName)#”)#
#Trim(“#XMLFormat(courseURL)#”)#
#Trim(“#XMLFormat(estEnrollment)#”)#
#Trim(“#XMLFormat(beginDate)#”)#
#Trim(“#XMLFormat(endDate)#”)#
#Trim(“#XMLFormat(banner)#”)#
#Trim(“#XMLFormat(header)#”)#
#Trim(“#XMLFormat(footer)#”)#
#Trim(“#XMLFormat(studentYearList)#”)#
#Trim(“#XMLFormat(navBorderVisibility)#”)#
#Trim(“#XMLFormat(lastDayForUsersToChangeSection)#”)#
#Trim(“#XMLFormat(allowOnlinePurchase)#”)#
#Trim(“#XMLFormat(ISBN)#”)#
#Trim(“#XMLFormat(purchasePrice)#”)#
#Trim(“#XMLFormat(XLEConversionDate)#”)#
#Trim(“#XMLFormat(job)#”)#
#Trim(“#XMLFormat(KHJobNumber)#”)#
#Trim(“#XMLFormat(GRTJobNumber)#”)#
#Trim(“#XMLFormat(author)#”)#
#Trim(“#XMLFormat(WPE)#”)#
#Trim(“#XMLFormat(isActive)#”)#
#Trim(“#XMLFormat(isPED)#”)#
“>
<cfset tempxml = “#tempxml# “>
This is a simple XML document that’s been generated by the ColdFusion code.
#tempxml#
#writePath#
Hi Carrie,
Your code is actually quite different from my example. My example built an XML document. Yours is just outputiing values from the first record of your query results. Actually, it’s not even doing that because there are no CFOUTPUTs around your output. That’s why it’s outputting the variable names rather than the query results.
That being said, don’t get too frustrated. This post was written quite a while ago and there are easier ways to approach this. Check out Adobe’s document Creating and saving an XML document object. It’s a much cleaner and simpler approach than the one I posted here.
You may also want to do a little reading on XML so that you have an understanding of what it is you’ll be generating before you start.
Good luck and thanks for visiting!
Glenn
HI Glen,
Just want to ask something.
I’m developing application in coldfusion and want to print the output in pdf. I’m using cfdocument but difficult to format the line and so on. So, is it possible to use scanned form and define the field using adobe lifecycle and extract data from mysql , load it to the pdf form and print it.
thanks in advance.
Amin
…. Just for the sake of it, if anyone is ACTUALLY looking for clean and simple, some databases will allow you to do this
SELECT name, age FROM tbluser ORDER BY name FOR XML RAW (‘user’) , ROOT(‘users’), ELEMENTS;
@anotherway: Yes, that would work for a very simple application running MS SQL 2005+. But if you want to cover a broader landscape of development, a more global and robust solution is often required.