CFXML: using variables as field names

[Guest]

I'm trying to make a system for creating database XML extracts as flexible as possible. Basically, a user can select from a dropdown list of form extracts, then, a DB reference table is read to see what columns from the database should be included in that extract.

For example, if the extract source table has 10 total columns, but the user wants only 5 of those to appear in the XML extract (which they will load in Excel), the reference DB table will have those 5 column names stored as values for that particular form type and the CF code will handle that dynamically.

I'm very close getting this to work.

Here's my problem: When I use CFXML to generate the extract, I'm using variable names for the XML field names in addition to the values, and the variable names are not getting resolved such that the XML extract isn't getting built properly.

What I'm trying to do is this:

<cfxml variable="TestFormat" casesensitive="no">
<extract>
<cfoutput query="GetFormData">
<form_ID>
<#column1#>#queryvar1#</#column1#>
<#column2#>#queryvar2#</#column2#>
.
.
.
</form_ID>
</cfoutput>
</extract>
</cfxml>

It's not resolving the variable field names when I do a dump of the XML. The static field <form_id> is ok. It's the variable field names that are the problem.

The dump should look like this:

extract
XmlText
form_ID XmlText
Full_Name XmlText chris donner
Company XmlText DOD

form_ID XmlText
Full_Name XmlText johnny kicker
Company XmlText Red Five

BUT it looks like this:

extract
XmlText
form_ID XmlText chris donner DOD
form_ID XmlText johnny kicker Red Five


Here's my code. I'm probably missing something simple...

Thanks for any ideas.

[Guest]

I figured it out. It wasn't evaluating this:

<CFSET xmlvar="&lt;" & #i# & "&gt;" & "#trim(var)#" & "&lt;" & "/" & "#i#" & "&gt;">
<cfoutput>#xmlvar#</cfoutput>

I changed the loop to this and now it works.

<cfloop list="#SelectedExtractColumns#" index="i">
<!---
** i = COLUMN NAME, e.g. Full_Name, Company, etc.
** var = COLUMN VALUE, e.g, Chris Thomas, Aspen Tech, Etc.
--->
<CFSET var=evaluate("getformdata.#i#")>

<CFIF i NEQ "FORM_ID">
<!---Build XML Field name, Value, /Field name e.g. <Full_Name>Joe Dee</Full_Name>--->
< <cfoutput>#i#</Cfoutput>><CFOUTPUT>#var#</CFOUTPUT></<cfoutput>#i#</CFoutput>>
</CFIF>
</cfloop>

[Newsgroup_User]

loamguy wrote:
> I figured it out. It wasn't evaluating this:
>
> <CFSET xmlvar="<" & #i# & ">" & "#trim(var)#" & "<" & "/" & "#i#" & ">">
> <cfoutput>#xmlvar#</cfoutput>
>
> I changed the loop to this and now it works.
>
> <cfloop list="#SelectedExtractColumns#" index="i">
> <!---
> ** i = COLUMN NAME, e.g. Full_Name, Company, etc.
> ** var = COLUMN VALUE, e.g, Chris Thomas, Aspen Tech, Etc.
> --->
> <CFSET var=evaluate("getformdata.#i#")>
>
> <CFIF i NEQ "FORM_ID">
> <!---Build XML Field name, Value, /Field name e.g.
> <Full_Name>Joe Dee</Full_Name>--->
> <
> <cfoutput>#i#</Cfoutput>><CFOUTPUT>#var#</CFOUTPUT></<cfoutput>#i#</CFoutput>>
> </CFIF>
> </cfloop>
>


If you care that could probably be simplified.

<cfoutput>
<cfloop list="#SelectedExtractColumns#" index = "i">
<cfif i NEQ "FORM_ID">
<#i#>#getFormdata #</#i#>
</cfif>
</cfloop>
</cfoutput>

That seems to be a little cleaner and easier to read and maintain to me.