How do I output an XML document object that is populated with SQL query data containing HTML content to an XML file in ColdFusion?

Question

I have written a script in ColdFusion that grabs all of the HTML content for my website in a database table and creates an XML document object. Below is my code so far.

<!--- Get page info --->
<cfquery name="qPageInfo" datasource="#application.datasource#">
    SELECT top 800 id, title, info, app
    FROM sitePublic
</cfquery>

<cfdump var="#qPageInfo#">

<!--- Creating XML document object w/ sitePublic data --->
 <cfxml variable="xmlPageInfo">
    <page>
        <cfoutput query="qPageInfo">
            <id>#qPageInfo.id#</id>
            <pagecontent>#Trim("#XMLFormat(qPageInfo.info)#")#</pagecontent>
        </cfoutput>
    </page> 
 </cfxml>

 <!---<cfdump var="#xmlPageInfo#">--->

 <!--- Need to figure out how to output to a file --->
 <cffile action="write" file="%filepath%/webcontent.xml" output="#qPageInfo#">

I was able to successfully do a cfdump on the XML document object and it dumped all content for all web pages in the browser window.

However, the part I cannot seem to get working is the last line, <cffile> to output the XML data to an XML file. Below is the error I get when trying to run execute this.

The expression has requested a variable or an intermediate expression result as a simple value. However, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values.
The most likely cause of the error is that you tried to use a complex value as a simple one. For example, you tried to use a query variable in a cfif tag.

I am new to ColdFusion so I could be leaving something out in my code, does anyone have an idea on how to fix this?

Nothing to do with your issue, but you do not need quotes or extra pound signs when nesting function calls. ie Use #Trim( XMLFormat(qPageInfo.info) )# instead of #Trim("#XMLFormat(qPageInfo.info)#")#. — Leigh
Shouldn't output="#qPageInfo#" be output="#xmlPageInfo#" You build the xml but then use the query output to write not the xml. — haxtbh
My apologies for the delayed response. Thank you Leigh and haxtbh for pointing those details out to me. — Default42

James A Mohler James A Mohler · Accepted Answer · 2016-07-08T14:55:12

I have a large and small concern about this

First

<!--- Creating XML document object w/ sitePublic data --->
<cfxml variable="xmlPageInfo">
<page>
    <cfoutput query="qPageInfo">
        <id>#qPageInfo.id#</id>
        <pagecontent>#Trim("#XMLFormat(qPageInfo.info)#")#</pagecontent>
    </cfoutput>
</page> 
</cfxml>

This is going to generate lots and lots of <id> and <pagecontent> tags

Consider

<pagecontent id="#qPageInfo.id#">#Trim(XMLFormat(qPageInfo.info))#</pagecontent>

Second

<cffile action="write" file="%filepath%/webcontent.xml" output="#qPageInfo#">

Now the real problem, qPageInfo is the query. I suspect that you wanted to write the xml.

The simpler approach is to deal with the XML as a string

<cfsavecontent variable="xmlPageInfo">
<page>
    <cfoutput query="qPageInfo">
        <pagecontent id="#qPageInfo.id#">#Trim(XMLFormat(qPageInfo.info))#</pagecontent>
    </cfoutput>
</page> 
</cfsavecontent>


<cffile action="write" file="%filepath%/webcontent.xml" output="#xmlPageInfo#">

OR

If you want to make sure you are processing xml and not just a string that is xml

<cfxml variable="xmlPageInfo">
<page>
    <cfoutput query="qPageInfo">
        <pagecontent id="#qPageInfo.id#">#Trim(XMLFormat(qPageInfo.info))#</pagecontent>
    </cfoutput>
</page> 
</cfxml>


<cffile action="write" file="%filepath%/webcontent.xml" output="#ToString(xmlPageInfo)#">

How do I output an XML document object that is populated with SQL query data containing HTML content to an XML file in ColdFusion?

2 Answers