1 Reply Latest reply on Mar 20, 2007 7:22 AM by insuractive

    Putting xml into a cfquery

    Anthony Spears Level 1
      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">
      <employer name="gas station">

      <year value="91">
      <employer name="bank">
      <employer name="gas station">

      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,

        • 1. Re: Putting xml into a cfquery
          insuractive Level 3
          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.