ColdFusion Tutorials - EasyCFM.COM

FOR XML and Cold Fusion
What the heck is FOR XML?

It's a type of query in Microsoft SQL Server that will return formatted XML data to you as a string basically. There are a host of options for FOR XML queries and I'm not going to get into that at all for this tutorial. Google 'FOR XML RAW AUTO EXPLICIT' for some good information.

Now, this would be much easier if Cold Fusion would allow you to refer to columns as an ordinal, but it doesn't as far as I know. You can acquire the column name and use that to get your column though. That would be great except that FOR XML queries use a generated column name which always contains a '-' and Cold Fusion doesn't like '-' characters in it's column names.

What to do, oh what to do.

Fear not! Query of Queries to the rescue. It's fairly self explanitory, but i'll explain a little. First we execute a FOR XML query to get a stream of XML data. Then we do a select from that where 0=1 so we don't get any results. In queries using UNION ALL the first result set will have the column names so we rename the column (there's only one) to 'xml' (can be anything that Cold Fusion can read) by performing a select from the original query where 0=1 so we don't get any results. Then we turn off debugging, switch the content type, and output the xml string.

<cfquery name="bad" datasource="myDSN">
    SELECT *
    FROM SOMETABLE FOR XML RAW
</cfquery>

<cfquery dbtype="query" name="good">
    select 'xml' as xmlstring from bad where 0=1
    union all
    select * from bad
</cfquery>

<cfsetting showDebugOutput="No">
<cfcontent type="text/xml">
<cfoutput query="good">
   #xmlstring#
</cfoutput>

All ColdFusion Tutorials By Author: Roy Ashbrook

Calling a DTS from Cold Fusion
This tutorial describes how to call a MS SQL DTS package from a cold fusion page using COM rather than a stored procedure.
Author: Roy Ashbrook
Views: 12,945
Posted Date: Monday, January 24, 2005
FOR XML and Cold Fusion
This script demonstrates how to dump FOR XML query data to the screen as xml in Cold Fusion
Author: Roy Ashbrook
Views: 10,405
Posted Date: Sunday, February 20, 2005