New here - Flash-guy, also new to ColdFusion and SQL (CF-Newbies doesn't
seem to be populated with anybody)
(...and before I ask the question, I'm also looking for a cleaner way to
write this, like not having to hard code the field column names)
How do I insert each subsequent record from a database into my XML file?
I got this working, but it of course repeats the first record in each
child node,which is not what I want.
<cfquery name="qAllLessons" datasource="coursesDB">
SELECT * FROM Lessons
</cfquery>
<cfquery name="qLessons" datasource="coursesDB">
SELECT ID, Lesson, Required, Time FROM Lessons
</cfquery>
<cfscript>
lrXML = XmlNew();
lrXML.xmlRoot = XmlElemNew(lrXML,"lData");
for (i = 1; i LTE #qAllLessons.RecordCount#; i = i + 1){
lrXML.lData.XmlChildren[i] =
XmlElemNew(lrXML,"Content");
lrXML.lData.XmlChildren[i].XmlChildren[1] =
XmlElemNew(lrXML,"ID");
lrXML.lData.XmlChildren[i].XmlChildren[2] =
XmlElemNew(lrXML,"Lesson");
lrXML.lData.XmlChildren[i].XmlChildren[3] =
XmlElemNew(lrXML,"Required");
lrXML.lData.XmlChildren[i].XmlChildren[4] =
XmlElemNew(lrXML,"Time");
lrXML.lData.XmlChildren[i].ID.XmlText = #qLessons.ID#;
lrXML.lData.XmlChildren[i].Lesson.XmlText =
#qLessons.Lesson#;
lrXML.lData.XmlChildren[i].Required.XmlText =
#qLessons.Required#;
lrXML.lData.XmlChildren[i].Time.XmlText =
#qLessons.Time#;
}
</cfscript>
<cfdump var=#lrXML#>
<cfset XMLText = ToString(lrXML)>
<cffile action="write"
file="C:\CFusionMX7\wwwroot\com\macromedia\test\myTextXml_3.xml"
output="#XMLText#">
This outputs:
<?xml version="1.0" encoding="UTF-8" ?>
<lData>
<Content>
<ID>1</ID>
<Lesson><b>Introduction to Spatial Analysis</Lesson>
<Required>True</Required>
<Time>2</Time>
</Content>
<Content>
<ID>1</ID>
<Lesson><b>Introduction to Spatial Analysis</Lesson>
<Required>True</Required>
<Time>2</Time>
</Content>
...etc...
How do I write the query to insert each record into each subsequent node
instead of inserting the first record each time?
Or, is there a cfscript out there that will take a table and convert its
contents to XML with the nodes being named what the field names are
named? I have searched the web, but haven't found one yet.
Thanks.
Jason Merrill | E-Learning Solutions | icfconsulting.com
NOTICE:
This message is for the designated recipient only and may contain privileged or
confidential information. If you have received it in error, please notify the
sender immediately and delete the original. Any other use of this e-mail by you
is prohibited.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211750
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
