As an CF-native alternative it should be possible to achieve a similar result using cfsavecontent to build the XML string - I'm pretty sure it uses Java stringBuffer behind the scenes - it's certainly orders of magnitude faster than using standard CF string concatenation.

I could have done that - but the white space would have been a bit much, not that it would have mattered between xml entities (afaik). I think for me it's more a stylistic choice.

Would it have been as slow using an actual CF-native XML instance using XMLNew()? Something similar to the <a href="http://www.cflib.org/udf.cf...">QueryToXML UDF</a>?

Interesting. Btw - don't forget you don't need to use html here. You can't actually. But URLs are auto-hot linked.

Anyway, as you know, when I wrote the CFC I did it from scratch ignoring the CFLib one. (Because I was bored. ;)

If you run a speed test, I'd love to know.

Found an issue and here is the fix.

When you have a date time that CF recognizes (at least with this oracle system this project is running on) it changes it to a ODBC style datestamp in the XML output! I changed your line ...

<cfelseif isSimpleValue(txt)>

to this...

<cfif isDate(txt)>
<cfset txt = dateFormat(txt,arguments.dateFormat) & " " & timeFormat(txt,arguments.timeFormat)>
<cfelseif isSimpleValue(txt)>

and of course added two cfargument tags with defaults.

Much better! I had downloaded your first toxml.cfc, and with a recordset of 2000 records, it was taking over 1 minute at 107000 ms. I couldn't understand what caused it to take so long. Glad I found your new download today, as it now runs much faster at about 500 ms. Thanks for the fix.

Troy, my new web site will have an easier project page for me to update, so stuff like this won't get lost as much.

John, I'll take a look at this later in the week.

John, I'm not seeing that. I passed in April 2, 2009 as a value, and it didn't mess with it. All I do is an XMLFormat. Maybe the Oracle output isn't XML safe? Can you send me a sample string?

OK... did you pass the value from an Oracle database?

Also... Oracle has the ability now to output XML based structure... and I am looking at doing that (just for Oracle at this point, based on Ben's famous.... "If you can do it in the database..." concept.)

Any ideas why I might be getting this error when using toxml.cfc?

XML Parsing Error: xml declaration not at start of external entity
Location: http://localhost:8300/reportsV2/runreport.cfm?CFID=2300&CFTOKEN=93572396
Line Number 2, Column 2: <?xml version="1.0" encoding="UTF-8"?>
--------^

thanks

This isn't an issue with toXML, but how you are displaying your XML. Your XML needs to be the very first thing output on a page. If you use

<cfcontent type="text/xml" reset="true">YOURXMLHERE

You should be ok. Note the reset="true",that nukes the earlier output, if any. Don't forget CF likes to output whitespace.

thanks!

Just wanted to say thanks for the CFC to convert various ColdFusion datatypes to XML packets. I am just getting into Spry now that I actually have some time at work and I was able to get the Auto Suggest feature to work and this CFC helped me quite a bit.

I too am trying to use the spry suggest widget with the output from your excellent query to xml function, but spry does not seem to like the syntax I am using. Could you post and example of the correct syntax?

I believe I did another blog post on Spry's suggest widget. I'd look there.

Thanks for the the great code! I extended it a bit for a different purpose. I use Oracle and stored procedures to send data back to Flex via a CFC. The stored procs use refcursors to send query data to the CFC which results in an array of objects in Flex. The problem comes in when I try to send a similar array back to Oracle. There are no cfargument types that let me do this easily. But I can convert the array into xml in the CFC and send it to Oracle as a CLOB! I load the data into an Oracle table or into a generic (temporary) table for further processing using the new 10g dbms_xmlstore PL/SQL utility. Problem solved!
I extended your code into someting that would convert an array of structures into Oracle XML format. I have included this function and an example using the generic approach. I need to do a bit more error trapping but I think you'll get the concept from what I've done.

ORACLE CODE:

Generic table Creation

CREATE TABLE XMLTABLE
(
ITEM_001 VARCHAR2(2000),
ITEM_002 VARCHAR2(2000),
ITEM_003 VARCHAR2(2000)
)

Procedure to upload XML

CREATE OR REPLACE PROCEDURE testxml(
indata IN CLOB,
status OUT VARCHAR2
)
IS
v_context DBMS_XMLSTORE.ctxtype;
v_rows NUMBER;
BEGIN
v_context := DBMS_XMLSTORE.newcontext('xmltable');
v_rows := DBMS_XMLSTORE.insertxml(v_context, indata);
DBMS_XMLSTORE.closecontext(v_context);
status := 'SUCCESS ' || TO_CHAR(v_rows);
EXCEPTION
WHEN OTHERS
THEN
status := 'UNKNOWN ERROR - ' || SQLERRM || ' (' || TO_CHAR(SQLCODE) || ')';
END testxml;

COLD FUSION CODE:

CONVERSION FUNCTION

<cffunction name="FlexArrayToOracleXML"
returnType="string"
access="public"
output="false"
hint="Converts an array of structures into XML for use with dbms_xmlstore">
<cfargument name="data"
type="array"
required="true"
hint="data array to convert">
<cfargument name="usekeys"
type="string"
default="YES"
hint="Use keys from structure or create generic ones (ITEM_xxx)">>
<cfargument name="setcase"
type="string"
default=""
hint="MIXED, LOWER, or '' (ucase) Sets case of all elements">>
<cfargument name="keyfilter"
type="string"
default=""
hint="Selects which keys from the structure to convert">>
<cfset var s = createObject('java','java.lang.StringBuffer').init("<?xml version=""1.0"" encoding=""UTF-8""?>")>

<cfset var keys = structKeyList(arguments.data[1])>
<cfset var KeyList = "">
<cfset var key = "">
<cfset var keylabel = 1>
<cfset KeyLabels = StructNew()>

<!-- filter which elements to move into xml -->
<cfif trim(keyfilter) NEQ "">
<cfset KeyList = "">
<cfloop index="key" list="#keyfilter#">
<cfif StructKeyExists(arguments.data[1], "#trim(key)#")>
<cfset KeyList = ListAppend(KeyList,"#key#")>
</cfif>
</cfloop>
</cfif>
<cfif trim(KeyList) EQ "">
<cfset KeyList = keys>
</cfif>

<!-- use keys in xml or make generic items -->
<cfif trim(usekeys) EQ "YES" OR trim(usekeys) EQ "Y">
<cfloop index="key" list="#KeyList#">
<cfswitch expression="#Trim(UCase(setcase))#">
<cfcase value="MIXED">
<cfset KeyLabels[key]=key>
</cfcase>
<cfcase value="LOWER">
<cfset KeyLabels[key]=LCase(key)>
</cfcase>
<cfdefaultcase>
<cfset KeyLabels[key]=UCase(key)>
</cfdefaultcase>
</cfswitch>
</cfloop>
<cfelse>
<cfloop index="key" list="#KeyList#">
<cfset KeyLabels[key] = "ITEM_" & RepeatString("0", 3-LEN(keylabel)) & keylabel>
<cfset keylabel = keylabel + 1>
</cfloop>
</cfif>

<!-- create XML -->
<cfset s.append("<ROWSET>")>
<cfloop index="x" from="1" to="#arrayLen(arguments.data)#">
<cfset s.append("<ROW>")>
<cfset keylabel = 1>
<cfswitch expression="#Trim(UCase(setcase))#">
<cfcase value="MIXED">
<cfloop index="key" list="#KeyList#">
<cfset s.append("<#KeyLabels[key]#>#xmlFormat(arguments.data[x][key])#</#KeyLabels[key]#>")>
</cfloop>
</cfcase>
<cfcase value="LOWER">
<cfloop index="key" list="#KeyList#">
<cfset s.append("<#KeyLabels[key]#>#xmlFormat(LCase(arguments.data[x][key]))#</#KeyLabels[key]#>")>
</cfloop>
</cfcase>
<cfdefaultcase>
<cfloop index="key" list="#KeyList#">
<cfset s.append("<#KeyLabels[key]#>#xmlFormat(UCase(arguments.data[x][key]))#</#KeyLabels[key]#>")>
</cfloop>
</cfdefaultcase>
</cfswitch>
<cfset s.append("</ROW>")>
</cfloop>
<cfset s.append("</ROWSET>")>

<cfreturn s.toString()>
</cffunction>

TEST CODE

<cfset myArray=ArrayNew(1)>

<cfset myStruct=StructNew()>
<cfset myStruct.FNAME="a">
<cfset myStruct.BEMSID="1">
<cfset myStruct.LNAME="b">
<cfset ArrayAppend(myArray,myStruct)>

<cfset myStruct=StructNew()>
<cfset myStruct.FNAME="c">
<cfset myStruct.BEMSID="2">
<cfset myStruct.LNAME="d">
<cfset ArrayAppend(myArray,myStruct)>

<cfset xmlStruct = FlexArrayToOracleXML(myArray,"NO")>
<cfdump var="#xmlParse(xmlStruct)#">

<CFTRY>
<cfset status = "TEST" />

<CFSTOREDPROC procedure = "mis.testxml"
datasource = "yourDSN
blockfactor="100"
returncode = "No">
<CFPROCPARAM type = "IN" cfsqltype = "CF_SQL_CLOB" value = "#xmlStruct#">
<CFPROCPARAM type = "OUT" cfsqltype = "CF_SQL_VARCHAR" variable="STATUS">
</CFSTOREDPROC>

<BR/><CFOUTPUT>#Now()# #status#</CFOUTPUT>
<CFCATCH type="any">
<CFDOCUMENT format="PDF" overwrite="yes" filename="Error.pdf">
<CFDUMP var="#cfcatch#"/>
</CFDOCUMENT>
<CFTHROW message="ERROR" />
</CFCATCH>
</CFTRY>