Skip to main content
A
Anonymous
February 23, 2010
Question

Creating XML from Multiple tables

  • February 23, 2010
  • 1 reply
  • 1170 views

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>

This topic has been closed for replies.

1 reply

D
Dan_Bracuk
Inspiring
February 23, 2010

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.

A
Anonymous
February 23, 2010

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.

J
JR__Bob__Dobbs-qSBHQ2
Inspiring
February 23, 2010

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.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices