NOT easy peasy.
When the query is returned to ColdFusion, it is broken up into smaller pieces, each in its own row (I understand this is because of the database driver). The column name is dynamic; you can't depend on it being anything in particular, or even staying what it was yesterday. I found various solutions on the Web, using a number of weird techniques (Query of Queries, looping, etc.) but I ran across one that utilizes several powerful features of recent versions of ColdFusion to output the whole thing in one hunk.
First: In your <cfquery> tag, make sure you specify the "result" attribute. I believe this was added in ColdFusion 8. We need it to figure out what the name of the column is from its property called "columnlist". (I almost always call it "query_result", but you can name it whatever you want.)
Second: Understand that you can reference a ColdFusion query using Struct-like notation. So you can see the contents of any cell by referencing it this way:
query name: my_query
columns: column_1, column_2, column_3
fifty rows returned
my_query["column_1"][10]
This would return the information in the 10th row of "my_query", in the "column_1" column. (Never forget that this is not an array of struct - the row number comes last, after the column name.)
Third: I discovered that you can supply a blank delimiter to "ArrayToList" and all of the elements of the list are concatenated. So:
<!--- Create my_array[1]='A', my_array[2]='B', my_array[3]='C' --->
<cfset my_array=['A','B','C']>
<cfoutput>#ArrayToList(my_array, "")#</cfoutput>
Will output a three-character string ABC.
Now: Put those pieces of information together and you can do this:
<cfquery datasource="my_datasource" name="my_xml_query" result="query_result">
SELECT * FROM my_table FOR XML AUTO, XMLSCHEMA
</cfquery> <cfset my_xml_string=ArrayToList(my_xml_query[query_result.columnlist],"")>
I'm certainly no Uncle Ben, but I thought this seemed like a pretty cool trick.
Posts
