Creating XML from Multiple tables

[bill_s]

I am looking for an example for creating an XML file from a query that has more than 1 table.  This would require looping and testing for data changes for  building various data elements.

In the example below, this xml would be the result of 2 tables.  I would have to test for change in Doc Id, Line Group Number, and Acct. Line.  Any change in this would require a new element.

Any sample code would be appreciated!

<?xml version="1.0"?>
<DOCUMENT DOC_CAT="JV" DOC_CD="JV" DOC_ID="TEST123">
  <JV_DOC_HDR>
    <DOC_CD Attribute="Y"><![CDATA[JV]]></DOC_CD>
    <DOC_ID Attribute="Y"><![CDATA[TEST123]]></DOC_ID>
  </JV_DOC_HDR>
  <JV_DOC_LNGRP AMSDataObject="Y">
    <DOC_CD Attribute="Y"><![CDATA[JV]]></DOC_CD>
    <DOC_ID Attribute="Y"><![CDATA[TEST123]]></DOC_ID>
    <DOC_LNGRP_LN_NO Attribute="Y"><![CDATA[1]]></DOC_LNGRP_LN_NO>
  </JV_DOC_LNGRP>
  <JV_DOC_ACTG AMSDataObject="Y">
    <DOC_CD Attribute="Y"><![CDATA[JV]]></DOC_CD>
    <DOC_ID Attribute="Y"><![CDATA[TEST123]]></DOC_ID>
    <DOC_LNGRP_LN_NO Attribute="Y"><![CDATA[1]]></DOC_LNGRP_LN_NO>
    <DOC_ACTG_LN_NO Attribute="Y"><![CDATA[1]]></DOC_ACTG_LN_NO>
    <FUND_CD Attribute="Y"><![CDATA[5010]]></FUND_CD>
    <DEPT_CD Attribute="Y"><![CDATA[1100]]></DEPT_CD>
    <UNIT_CD Attribute="Y"><![CDATA[9120]]></UNIT_CD>
    <RSRC_CD Attribute="Y"><![CDATA[4224]]></RSRC_CD>
    <ACTV_CD Attribute="Y"><![CDATA[4193]]></ACTV_CD>
  </JV_DOC_ACTG>
  <JV_DOC_ACTG AMSDataObject="Y">
    <DOC_CD Attribute="Y"><![CDATA[JV]]></DOC_CD>
    <DOC_ID Attribute="Y"><![CDATA[TEST123]]></DOC_ID>
    <DOC_LNGRP_LN_NO Attribute="Y"><![CDATA[1]]></DOC_LNGRP_LN_NO>
    <FUND_CD Attribute="Y"><![CDATA[5010]]></FUND_CD>
    <DEPT_CD Attribute="Y"><![CDATA[1100]]></DEPT_CD>
    <UNIT_CD Attribute="Y"><![CDATA[9120]]></UNIT_CD>
    <RSRC_CD Attribute="Y"><![CDATA[4224]]></RSRC_CD>
    <ACTV_CD Attribute="Y"><![CDATA[4193]]></ACTV_CD>
  </JV_DOC_ACTG>
</DOCUMENT>
</XML_EXPORT_FILE>

[Dan_Bracuk]

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.

[bill_s]

Thanks Dan,

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.

[JR__Bob__Dobbs-qSBHQ2]

bill s,

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.

3. A sample of a query used to get the data that will be used to create the XML documents.  This should include a sample result set.4. Your database's table structure.5. The CF code you've tried along with any error messages you receive.

[bill_s]

Hi Bob,

In response:

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_actg.unit_cd

FROM jv_doc_hdr,

jv_doc_lngrp,

jv_doc_actg

WHERE (

(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.

Thanks!

Bill

[Dan_Bracuk]

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.

[bill_s]

Awe...that makes sense. I think that is exactly what I need!

Thank You!

[Dan_Bracuk]

Here is a very short extract from something I wrote.

<Patients>
<cfoutput query="q1">
<Patient>
<cfset i = 1>
<cfloop list="#ShortTagList#" index="tag">
<cfscript>
ThisField = ListGetAt(ShortFieldList, i);
i = i + 1;
</cfscript>
<cfif len(trim(q1[ThisField][currentrow])) gt 0>
<#tag#>#q1[ThisField][currentrow]#</#tag#>
</cfif>

</cfloop>

</Patient>
</cfoutput>
</Patients>