Display list of accounts with subaccounts?

Question

Hi,

I have a page which receives xml from another web site, so I have no control over the xml. It is returning a list of Accounts. Specifically, QuickBooks accounts. These accounts can have children. However, there isn't a "has child" attribute, only a "has parent" attribute. In other words, if you look at an account you could tell that it had a parent, but you would have no idead if it had any children.

Here's an example of two accounts, one a child of the other.

The child:

<Account> 
  <Id idDomain="QB">288</Id> 
  <SyncToken>1</SyncToken> 
  <MetaData> 
    <CreatedBy>app</CreatedBy> 
    <CreatedById>1</CreatedById> 
    <CreateTime>2010-06-16T21:47:58.0Z</CreateTime> 
    <LastModifiedBy>app</LastModifiedBy> 
    <LastModifiedById>1</LastModifiedById> 
    <LastUpdatedTime>2010-06-16T21:47:58.0Z</LastUpdatedTime> 
  </MetaData> 
  <ExternalKey idDomain="QB">288</ExternalKey> 
  <Synchronized>true</Synchronized> 
  <Name>Property Tax Reserve</Name> 
  <AccountParentId idDomain="QB">272</AccountParentId> 
  <AccountParentName>Bank of the West - MMA</AccountParentName> 
  <Active>true</Active> 
  <Type>Asset</Type> 
  <Subtype>Bank</Subtype> 
  <CurrentBalance>0</CurrentBalance> 
</Account> 

The parent:

<Account> 
  <Id idDomain="QB">272</Id> 
  <SyncToken>1</SyncToken> 
  <MetaData> 
    <CreatedBy>app</CreatedBy> 
    <CreatedById>1</CreatedById> 
    <CreateTime>2009-03-19T21:34:22.0Z</CreateTime> 
    <LastModifiedBy>app</LastModifiedBy> 
    <LastModifiedById>1</LastModifiedById> 
    <LastUpdatedTime>2009-03-19T21:34:22.0Z</LastUpdatedTime> 
  </MetaData> 
  <ExternalKey idDomain="QB">272</ExternalKey> 
  <Synchronized>true</Synchronized> 
  <Name>Bank of the West - MMA</Name> 
  <Active>true</Active> 
  <Type>Asset</Type> 
  <Subtype>Bank</Subtype> 
  <CurrentBalance>4625.93</CurrentBalance> 
</Account> 

So in a list of a bunch of Accounts (in no particular order) how would I loop through to display them in a hierarchical fashion in ColdFusion?

Ex.

• Bank of the West - MMA
  • Property Tax Reserve
• Parent Item
  • child 1
  • child 2

etc..

Answer 1

Assuming your accounts are all coming in the same XML file you can use XPath to find the parents and children.

<cfscript>
xmlDoc=XMLParse("yourfile.xml");
listParents = XmlSearch(xmlDoc, "//Account[not(AccountParentName)]");
writeoutput("<ul>");
for (i = 1; i LTE ArrayLen(listParents); i = i + 1) {
    writeoutput("<li>" & listParents[i].Name.XmlText);
    listChildren = XmlSearch(xmlDoc, "//Account[AccountParentId=#listParents[i].Id.XmlText#]");
    if(ArrayLen(listChildren)) {
        writeoutput("<ul>");
        for (i = 1; i LTE ArrayLen(listChildren); i = i + 1) {
            writeoutput("<li>" & listChildren[i].Name.XmlText);
        }
        writeoutput("</ul>");
    }
    writeoutput("</li>");

}
writeoutput("</ul>");
</cfscript>

Note : "Accounts" is your root node in this example. You didn't specify your root node name. Edit : Changed variable names for clarity

Answer 2

I would not attempt to do it in a single pass. Instead, I'd process the XML and put it into a relational database or even just turn the XML into sensible json and store it in MongoDB. Then, you can query it however you want and work with the data with familiar data structures.

Just because XML is the interchange format doesn't mean you have to work with it that way. You're letting the data format, which you don't control, control how you display data.