Putting xml into a cfquery

[Anthony Spears]

I am having problems when an xml tag is not always present.

The xml looks something like this.
<year value="90" EMPcount="2">
<employer name="bank">
<wages>2000.00</wages>
</employer>
<employer name="gas station">
<wages>1000.00</wages>
</employer>
</year>

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

I want the query to look like this.

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

I can create the query. I can populate it with rows with the tags that are always present. I just can't seem to figure out the logic to put that tag that is only there sometimes.

Can anyone help?
Thanks in advance,

Anthony

[p6studio]

i guess create a column and make the value null when it doesn exist?