Putting xml into a cfquery

[Anthony Spears]

This is the situation. I want to build a query where I enter the value of a parent tag for every row where there are multiple children. xml looks something like this.

<year value="90">
<employer name="bank">
<wages>2000.00</wages>
</employer>
<employer name="gas station">
<wages>1000.00</wages>
</employer>
</year>

<year value="91">
<employer name="bank">
<wages>2500.00</wages>
</employer>
<employer name="gas station">
<wages>1500.00</wages>
</employer>
</year>

I want the query to look like this.

year employer wages
90 bank 2000.00
90 gas station 1000.00
91 bank 2500.00
91 gas station 1500.00

I think I have to do a loop within a loop, but i can't figure it out.

Thanks in advance,

Anthony

[insuractive]

Its probably going to wind up being something like this:

1) Build an empty Query
2) Parse your XML using XMLParse()
3) Select all nodes matching XPATH: "/year" or "//year" using XMLSearch()
4) XMLSearch returns an array, loop over the resulting array (1 record in the array = 1 row in your query)
5) Extract the data from your XML node (use CFDUMP to figure out the syntax)
6) Create a new row in your query, add data
7) Do the happy my-cf-code-works dance.