ToXML Update

coldfusion (This post is more than 2 years old.)

I'm currently working on the 5.1 version of BlogCFC. As part of my set of updates, I'm changing the entry editor to use Spry to handle selecting related entries. This worked fine, but I noticed that the speed improvement wasn't what I thought it would be.

I did some testing with one of the most useful tags out there, cftimer, and discovered that the major slowdown in the process was my toXML code.

I had forgotten that string manipulation can be a bit slow in ColdFusion. Since I was converting about 800 rows of data to XML, the slowdown wasn't surprising. I changed the code to use Java's StringBuffer class. This is much more efficient when doing a large number of string changes.

How did it work? I was seeing execution times of 1000 ms to create my XML packet. When I switched to using StringBuffer, the time went to under 100 ms. To me, that's a good improvement.

I've attached the zip to this entry. For those who don't remember, the purpose of this CFC is to let you convert various ColdFusion datatypes to XML packets. Yes you can do this with cfwddx, I wanted something that gave me more control over the XML so that I could use it with Spry. Enjoy.

Download attached file.

Hire Me!

I'm currently looking for my next role in developer evangelism and advocacy. I have a long history of helping companies work with developers and love to write, create demos, and present at conferences. You can find my resume to learn more and drop me an email (raymondcamden@gmail.com) to reach out.

Support this Content!

If you like this content, please consider supporting me. You can become a Patron, visit my Amazon wishlist, or buy me a coffee! Any support helps!

Want to get a copy of every new post? Use the form below to sign up for my newsletter.

Archived Comments

Comment 1 by Ed posted on 7/13/2006 at 5:42 PM

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.

Comment 2 by Raymond Camden posted on 7/13/2006 at 5:54 PM

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.

Comment 3 by Nathan Dintenfass posted on 7/13/2006 at 6:05 PM

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

Comment 4 by Raymond Camden posted on 7/13/2006 at 6:11 PM

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.

Comment 5 by John Farrar posted on 7/19/2006 at 10:15 PM

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.

Comment 6 by Troy Pullis posted on 7/20/2006 at 12:39 AM

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.

Comment 7 by Raymond Camden posted on 7/20/2006 at 12:44 AM

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.

Comment 8 by Raymond Camden posted on 7/21/2006 at 12:25 AM

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?

Comment 9 by John Farrar posted on 7/31/2006 at 7:16 PM

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

Comment 10 by Carrie posted on 7/31/2007 at 8:28 PM

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

Comment 11 by Raymond Camden posted on 7/31/2007 at 8:39 PM

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.

Comment 12 by Carrie posted on 8/1/2007 at 12:29 AM

thanks!

Comment 13 by Malik Robinson posted on 8/23/2007 at 3:19 AM

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.

Comment 14 by Paul posted on 10/26/2007 at 6:40 PM

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?

Comment 15 by Raymond Camden posted on 10/27/2007 at 12:29 AM

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

Comment 16 by Warren posted on 11/10/2007 at 9:04 PM

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>