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

12 thoughts on “Output ColdFusion Query Results to XML

  1. 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.

  2. 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#

  3. 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

  4. 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

  5. …. 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;

  6. @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.

  7. I am using a DW extension that references a file called data.xml to populate the flash accordion. I need to generate the data dynamically and I think I can figure out how to modify your code to make it work for me. I assume the original file data.xml must be deleted, and I generate the new data.xml programmatically through a page i could call data.cfm. How do I reference the data.xml file in the actual element page if the file is not yet generated? Do I reference the data.cfm file instead? I am sure i am missing something and I could use some help.

    Here is a copy of the data.xml file:

    RS2 FORGED
    nder a new program.01-10-2010
    The classic Racing Dynamics design is a high performance classic.

    NEW PRICING ON THE RS2-F PROGRAM
    01-01-2010
    The Racing Dynamics RS2, our most popular wheel, is now available with new applications and new pricing.

    M3 SWAY BARS
    01-01-2010
    <!— Caption 2 —>

    MAXIMUM PERFORMANCE FROM THE HOLLOW BARS
    <!— 01-01-2010 —>
    Text 2

    F10 LOWERINK KIT
    01-01-2010
    <!— Caption 3 —>

    SPORT SPRINGS FOR THE NEW 5 SERIES
    <!— 01-01-2010 —>
    Text 3

    NEWS 4
    01-01-2010
    Caption 3

    NEWS 4
    01-01-2010
    Text 3

    NEWS 4
    01-01-2010
    Caption 3

    NEWS 4
    01-01-2010
    Text 3

    NEWS 4
    01-01-2010
    Caption 3

    NEWS 4
    01-01-2010
    Text 3

    NEWS 4
    01-01-2010
    Caption 3

    NEWS 4
    01-01-2010
    Text 3

    NEWS 4
    01-01-2010
    Caption 3

    NEWS 4
    01-01-2010
    Text 3

    NEWS 4
    01-01-2010
    Caption 3

    NEWS 4
    01-01-2010
    Text 3

    NEWS 4
    01-01-2010
    Caption 3

    NEWS 4
    01-01-2010
    Text 3

Comments are closed.