Creating a hierarchical xml structure with ColdFusion

Can someone help me improve on this? There has to be a better way. What I am doing is building a local web service for flex to grab in order to populate a tree menu. Flex being the application I'm calling the web service is just some background but has nothing to do with the problem.

In order to create hierarchical data for this tree, I've coded the following.

    <cffunction name="getFormsBinMenu" access="remote" returntype="string">
    <cfquery name="getParents" datasource="db_intranet_data">
        SELECT * FROM formsbin_categories WHERE parentid = 1 ORDER BY sortorder ASC
    </cfquery>

    <cfoutput>
        <cfxml variable="formsBinMenu">
            <?xml version='1.0' encoding='utf-8' ?>
            <folder label="Forms Bin">
                <cfloop query="getParents">
                    <folder label="#XMLFormat(getParents.catname)#">
                            <cfquery name="getSubParents" datasource="db_intranet_data">
                                SELECT * FROM formsbin_categories WHERE parentid = #catid# and testonly = 0 and visible = 1 ORDER BY sortorder ASC
                            </cfquery>
                            <cfloop query="getSubParents">
                                <folder label="#XMLFormat(getSubParents.catname)#">
                                          <cfquery name="getNextSubParents" datasource="db_intranet_data">
         开发者_Go百科                                       SELECT * FROM formsbin_categories WHERE parentid = #catid# and testonly = 0 and visible = 1 ORDER BY sortorder ASC
                                            </cfquery>
                                            <cfloop query="getNextSubParents">
                                                <folder label="#XMLFormat(getNextSubParents.catname)#"/>
                                            </cfloop>                        
                                </folder>   
                            </cfloop>                                         
                    </folder>
                </cfloop>
            </folder>
        </cfxml>
    </cfoutput>

    <cfset menu =  #toString(formsBinMenu)#>
    <cfreturn menu>
</cffunction>

As you can see I'm just looping through different queries. While this works for my purpose, how could I code it so that there isn't so much code repetition?

I thought about checking to see how many levels deep the structure would be and then running it all in an index loop but that still seems like too much overhead.

Any suggestions would be great!

Take a look at the code I created using xslt transformation, I'm sure you can use the same approach in your code (note that parentID in my query is a foreign key of brandID):

<cfquery name="queryBrands" datasource="#dsn#">
    SELECT brandID, brand, isAssignable, isnull(parentID, 0) AS parentID, abbreviation
    FROM dbo.BrandTree
</cfquery>


<cfxml variable="rawNodeTree">
    <cfoutput>
        <nodes>
            <cfloop query="queryBrands">
                <node id="#queryBrands.brandID#"
                      parentID="#queryBrands.parentID#"
                      name="#XmlFormat(queryBrands.brand)#"
                      isAssignable="#queryBrands.isAssignable#"
                      abbreviation="#queryBrands.abbreviation#" />
            </cfloop>
        </nodes>
    </cfoutput>
</cfxml>

<cfxml variable="xslt">
    <xsl:transform version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
        <xsl:template match="/nodes">
            <nodes>
                <xsl:call-template name="getChildNodes" />
            </nodes>
        </xsl:template>

        <xsl:template name="getChildNodes">
            <xsl:param name="parentID" select="0" />

            <xsl:for-each select="//node[ @parentID = $parentID ]">
                <xsl:sort select="@name" />

                <node id="{@id}"
                      parentID="{@parentID}"
                      name="{@name}"
                      isAssignable="{@isAssignable}"
                      abbreviation="{@abbreviation}">
                      <xsl:call-template name="getChildNodes">
                        <xsl:with-param name="parentID" select="@id" />
                      </xsl:call-template>
                </node>
            </xsl:for-each>
        </xsl:template>
    </xsl:transform>
</cfxml>

<cfset result = xmlTransform(rawNodeTree, xslt) />

I believe when I was researching how to solve this same issue I used http://www.bennadel.com/blog/1080-Recursive-XSLT-For-Nested-XML-Nodes-In-ColdFusion.htm to help me out.

Another option is to use CTE for your query then use fields from that to build your xml. (if you're using ms sql server)