It would be simpler to write a single query that gets the necessary data from both tables. If you don't know how, I have heard good things about the book, Teach Yourself SQL in 10 Minutes, by Ben Forta.
I under the SQL and getting the data. It's putting it into the correct XML format (my sample) that I am having trouble starting using CF.
Could you elaborate on your requirements and database structure? It would be beneficial to have the following items.
1. Your CF version.
2. Your database version (MS SQL Server, MySQL, etc). Some databases have built in XML capabilities, using DB specific features rather than CF might be an option.
Here is a very short extract from something I wrote.
<cfset i = 1>
<cfloop list="#ShortTagList#" index="tag">
ThisField = ListGetAt(ShortFieldList, i);
i = i + 1;
<cfif len(trim(q1[ThisField][currentrow])) gt 0>
1) We are running CF8
2) Oracle 10G
3) (sample query)
SELECT jv_doc_hdr.doc_cd, jv_doc_hdr.doc_id, jv_doc_lngrp.doc_lngrp_no,
jv_doc_actg.doc_actg_ln_no, jv_doc_actg.fund_cd, jv_doc_actg.dept_cd,
(jv_doc_hdr.doc_cd = jv_doc_lngrp.doc_cd)
AND (jv_doc_hdr.doc_dept_cd = jv_doc_lngrp.doc_dept_cd)
AND (jv_doc_hdr.doc_id = jv_doc_lngrp.doc_id)
AND (jv_doc_hdr.doc_vers_no = jv_doc_lngrp.doc_vers_no)
AND (jv_doc_lngrp.doc_cd = jv_doc_actg.doc_cd)
AND (jv_doc_lngrp.doc_dept_cd = jv_doc_actg.doc_dept_cd)
AND (jv_doc_lngrp.doc_id = jv_doc_actg.doc_id)
AND (jv_doc_lngrp.doc_vers_no = jv_doc_actg.doc_vers_no)
AND (jv_doc_lngrp.doc_lngrp_no = jv_doc_actg.doc_lngrp_no)
4) For simplicity, assume that the database columns are text. Joins in the Where clause are the unique key structures.
5) I have not contructed anything. I am in search of some "starter code" and then could take it from there. From what I have seen on the forums, most of the XML samples were from a single table and just a straight dump. In my case, I need to test for changes in query results for Doc ID, Lngrp, or doc_actg_ln_no before writing to the file.
Regarding, "I need to test for changes in query results for Doc ID, Lngrp, or doc_actg_ln_no before writing to the file."
If you order by those fields in your query, you can use the group attribute of cfoutput and then you might not have to check anything.
Awe...that makes sense. I think that is exactly what I need!