ColdFusion - how to loop through XML output and adding to struct or array?

Question

I am trying to create a list of Chapter Officers and their respective positions. The data comes from a series of XML key/value pairs accessed via web service (Key: Member_Name, Value: Joe Member. Key: Position_Name, Value: President, and so forth.) Each officer for a given chapter has their own Member_Name and Position_Name.

The API I am working with will only return an entire object, so I set up an array to convert the XML name and hold everything:

<cfset keyValue = xmlSearch(soapBody,"//*[local-name()='KeyValueOfstringanyType']") />

My thought was to loop through that array, and for every instance of the key Member_Name and Position_Name, add the values to a struct:

<cfset chapterOfficers=structNew()>
<cfloop index="i" from="1" to="#arrayLen(keyValue)#">
    <cfif keyValue[i].Key.xmlText EQ 'Member_Name'>
        <cfset chapterOfficers.Name=keyValue[i].Value.xmlText>
    </cfif>
    <cfif keyValue[i].Key.xmlText EQ 'Position_Name'>
        <cfset chapterOfficers.Position = keyValue[i].Value.xmlText>
    </cfif>
    <cfif keyValue[i].Key.xmlText EQ 'Term_Name'>
        <cfset chapterOfficers.Term = keyValue[i].Value.xmlText>
    </cfif>
</cfloop>

Dumping this structure gives me a nice neat little table with one person's name, that person's position, and their term -- but only that one (which happens to be the last entry in the XML file). Even adding i=i+1 does not have any effect -- it's almost like the loop is starting at the end, and not continuing.

I've tried other ways of adding things to the structure, which does loop through everything, but the key/value pairs come out in an unrelated order. I know that structs are not ordered anyways, but I need to have some way to sequence the data from the XML output. I also tried various other loops, trying to add a series of little structs like this one to an array. It worked, but again, only for that one person -- no actual "looping" seemed to take place! I can see all of the information I need, all at the same time -- so maybe it's in putting it all in order that I am doing something wrong?

Thank you all in advance, I appreciate any suggestions or nudges in the right direction!

UPDATE: don't know if this helps, but just now I swapped the values of To and From in the loop I am using, and set the step to -1, and it gave me the FIRST person in the list. But still did not loop.

UPDATE: Thanks Peter, here is an example of the XML I am working with:

<b:KeyValueOfstringanyType>
                    <b:Key>Member_Guid</b:Key>
                    <b:Value i:type="d:string">006e1c09-25f9-4178-86de-13c3e63200ce</b:Value>
                 </b:KeyValueOfstringanyType>
                 <b:KeyValueOfstringanyType>
                    <b:Key>Member_Type</b:Key>
                    <b:Value i:type="d:string">Entity</b:Value>
                 </b:KeyValueOfstringanyType>
                 <b:KeyValueOfstringanyType>
                    <b:Key>Member_Name</b:Key>
                    <b:Value i:type="d:string">Member, Joe</b:Value>
                 </b:KeyValueOfstringanyType>
                 <b:KeyValueOfstringanyType>
                    <b:Key>Position_Guid</b:Key>
                    <b:Value i:type="d:string">02ae1c09-5779-4891-8cd1-05cf475cf5af</b:Value>
                 </b:KeyValueOfstringanyType>
                 <b:KeyValueOfstringanyType>
                    <b:Key>Position_Type</b:Key>
                    <b:Value i:type="d:string">CommitteePosition</b:Value>
                 </b:KeyValueOfstringanyType>
                 <b:KeyValueOfstringanyType>
                    <b:Key>Position_Name</b:Key>
                    <b:Value i:type="d:string">President</b:Value>
                 </b:KeyValueOfstringanyType>
                 <b:KeyValueOfstringanyType>
                    <b:Key>Term_Guid</b:Key>
                    <b:Value i:type="d:string">044e1c09-a90b-495f-891f-afa13e653dee</b:Value>
                 </b:KeyValueOfstringanyType>
                 <b:KeyValueOfstringanyType>
                    <b:Key>Term_Type</b:Key>
                    <b:Value i:type="d:string">CommitteeTerm</b:Value>
                 </b:KeyValueOfstringanyType>
                 <b:KeyValueOfstringanyType>
                    <b:Key>Term_Name</b:Key>
                    <b:Value i:type="d:string">2011-2012</b:Value>
                 </b:KeyValueOfstringanyType>

Repeats for every chapter officer on file.

UPDATE: here is the code I came up. It does what I want it to do, but there are much better ways to do it, I am sure...

First I get the results from the SOAP response, "drill down" to the level I need, and then strip out the xml-specific stuff and get the data into a usable array:

<cfset soapBody = xmlParse(cfhttp.fileContent)>
<cfset soapBody = soapBody['s:Envelope']['s:Body'].QueryResponse.QueryResult.Objects.anyType.Fields />
<cfset keyValue = xmlSearch(soapBody,"//*[local-name()='KeyValueOfstringanyType']") />

Then

<cfset chapterOfficers=arrayNew(2)>
<cfset x=1>
<cfset y=1>

<cfloop index="i" from="1" to="#arrayLen(keyValue)#">
    <cfif keyValue[i].Key.xmlText EQ 'Member_Name'>
        <cfset memberName = keyValue[i].Value.xmlText>
        <cfset chapterOfficers[x][y]=#memberName#>
        <cfset y=y+1>
    </cfif>
    <cfif keyValue[i].Key.xmlText EQ 'Position_Name'>
        <cfset positionName = keyValue[i].Value.xmlText>
        <cfset chapterOfficers[x][y]=#positionName#>
        <cfset x=x+1>
        <cfset y=1>
    </cfif>
    <cfif keyValue[i].Key.xmlText EQ 'Member_Guid'>
        <cfset memberGuid = keyValue[i].Value.xmlText>
        <cfset chapterOfficers[x][3]=#memberGuid#>
    </cfif>
</cfloop>

I do some other processing, checking for existence of variables, etc, and then output the names of the Officers and their respective positions with

<cfloop from="1" to="#arrayLen(chapterOfficers)#" index="x">
    <p>
        <cfoutput><a href="OfficerDetail.cfm?sessionGuid=<cfoutput>#URL.sessionGuid#</cfoutput>&memberGuid=<cfoutput>#chapterOfficers[x][3]#</cfoutput>">#chapterOfficers[x][1]#</a></cfoutput><br />
        <cfoutput>#chapterOfficers[x][2]#</cfoutput><br />
    </p>
</cfloop>

I was able to add Member_Guid to the array and use it so site visitors can click on a person's name to see further detail (company, email address, etc). And that is about it! What do you think? Again, thanks a lot for taking the time, I really appreciate it!

Answer 1

Here's how I would probably solve this:

<cfset var ChapterOfficers = StructNew()>
<cfset var CurMemberGuid = '' />

<cfloop index="local.CurPair" array=#keyValue#>

    <cfif CurPair.Key.XmlText EQ 'Member_Guid' >
        <cfset CurMemberGuid = CurPair.Value.XmlText />
        <cfset ChapterOfficers[CurMemberGuid] = StructNew() />
    <cfelse>
        <cfset ChapterOfficers[CurMemberGuid][CurPair.Key.XmlText] = CurPair.Value.XmlText />
    </cfif>

</cfloop>

It uses the existing XmlSearch you've done, and assumes Member_Guid is always the first key/value pair. I've used var/local scope assuming this is going inside a function (which it probably should be), but if not just remove them.

It uses structs so lookup of a particular GUID is easy, but order isn't preserved (though if necessary you could keep a separate array to do that), and you don't have to remember which array position matches to which key.

If you wanted to lookup based on other fields, you could also convert the data into a query, like so:

<cfset var ChapterOfficers = QueryNew('Member_Guid,Member_Type,Member_Name,Position_Guid,Position_Type,Position_Name,Term_Guid,Term_Type,Term_Name')>
<cfset var CurRow = 1 />

<cfloop index="local.CurPair" array=#keyValue#>

    <cfif CurPair.Key.XmlText EQ 'Member_Guid' >
        <cfset QueryAddRow(ChapterOfficers) />
    </cfif>

    <cfset QuerySetCell(ChapterOfficers,CurPair.Key.XmlText,CurPair.Value.XmlText) />

</cfloop>

This maintains order and makes more general lookup easier, also makes it easier if your main use is outputting direct to HTML.

I've hard-coded the column keys there, but you could also do a pre-loop to collate those first, if they are something that is liable to change.

Hopefully this all makes sense?

Answer 2

In Coldfusion 10 or Railo 4, you could use the Underscore.cfc library to help clean your solution up a lot:

<cfscript>
    soapBody = XmlParse(cfhttp.filecontent);
    fields = xmlSearch(soapBody,"//*[local-name()='Fields']");
    chapterOfficers = _.map(fields, function (field) {
        var officer = {};
        _.each(field.xmlChildren, function (KeyValueOfstringanyType) {
            var key = KeyValueOfstringanyType['b:Key'].xmlText;
            var value = KeyValueOfstringanyType['b:Value'].xmlText;
            officer[key] = value;
        });
        return officer;
    });
</cfscript>

<cfoutput>
<cfloop array="#chapterOfficers#" index="officer">
    <a href="OfficerDetail.cfm?sessionGuid=#URL.sessionGuid#&memberGuid=#officer.Member_Guid#">#officer.Member_Name#</a> 
    #officer.Position_Name#<br />
</cfloop>
</cfoutput>

Now isn't that nicer? I'm not exactly sure what your SOAP response looks like, but you should be able to tweak the xmlSearch() to match on the parent element of KeyValueOfstringanyType. I also removed all the unnecessary cfoutputs for you. Also, I'd recommend switching to JSON instead of XML. It's a LOT easier to parse.

(Disclaimer: I wrote the Underscore.cfc library)