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.

<!--
    Name: forxml.cfm
    author: Roy Ashbrook
    email: royashbrook@yahoo.com 
    description:
    this script demonstrates how to dump for xml query data to the screen as xml 
-->


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

About This Tutorial
Author: Roy Ashbrook
Skill Level: Intermediate 
 
 
 
Platforms Tested: CF5,CFMX
Total Views: 86,456
Submission Date: February 20, 2005
Last Update Date: June 05, 2009
All Tutorials By This Autor: 2
Discuss This Tutorial
  • after playing with it a little, i find more success using the following: #xmlstring# instead of: #xmlstring# it really depends on how you want to check your string before you output it. when i had a need for this i was going to be writing data out and the receiving service i wrote would perform some replacements etc because the data might not display properly in ie because of language types etc, but you could check the data server side based on your rules. it's up to you, this is just supposed to show how to get around the issue of mssql naming it's xml column some silly name that i can't use in cf templates. =) hopefully that helps a little more.

  • i'd need a little more information. all i did when i posted this was remove the query and replace it with a bogus query. note that in ie it will throw an error unless you wrap it with a root node. i didn't put that in because rss readers or some other automated reader might not want that. you can try using this: #xmlstring# instead of this: #xmlstring# and that may help. note that this sample page relies on the creator to ensure the data is properly formatted for xml. it doesn't actually parse it to validate it is good xml so if you have an invalid character or something it may throw an error as well. hopefully that helps. =)

  • I tried the above code, but the xmlstring doesnot display for me.. i am getting just a blank page..

Advertisement


Website Designed and Developed by Pablo Varando.