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.

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.

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.

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?

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...
Line Number 2, Column 2:   <?xml version="1.0" encoding="UTF-8"?>
--------^

thanks

thanks!

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?

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>