replace setcell with insert query, pretty much (first example is pretty easy to read)
By: Drew Tempelmeyer
A lot of people want to integrate XML into their ColdFusion applications but they don't know how to parse it. Parsing XML with ColdFusion MX is really simple. If you know me and have seen some of my applications, I use cfscript a lot. I've just recently completed cfblog which is a ColdFusion blog system; once it was completed, I needed something else to do. I know I don't submit a lot of tutorials here, but usually when I submit one, it'll be good (maybe).
What we'll be using:
- cfhttp
- xmlparse
- QueryNew
- QueryAddRow
- QuerySetCell
- cfquery
- cfoutput
- cfscript
To understand this tutorial, you will have to know the basic concpets of cfscript, and the Query functions.
I commented the code when I was writing it so in a way, it is its own tutorial :P
<cfhttp url="http://www.xp-resources.com/easycfm.xml" method="GET">
<cfscript>
xmlfile = xmlparse(cfhttp.filecontent); //Parses the XML
xmlsize = arraylen(xmlfile.Tutorials.xmlchildren); //Tutorials is the parent tree
xmlqry = QueryNew("id, title, url, author, platform, descr"); //Sets a query for output
QueryAddRow(xmlqry,xmlsize);
for(a=1;a LTE xmlsize;a=a+1) {
QuerySetCell(xmlqry,"title",xmlfile.Tutorials.TutorialID[a].Title.xmlText,a);
//xmlfile.Tutorials.TutorialID[a].Title.xmlText gets the text of the title for the current tutorial (xmlText)
QuerySetCell(xmlqry,"url",xmlfile.Tutorials.TutorialID[a].URL.xmlText,a);
QuerySetCell(xmlqry,"author",xmlfile.Tutorials.TutorialID[a].Author.xmlText,a);
QuerySetCell(xmlqry,"platform",xmlfile.Tutorials.TutorialID[a].Platform.xmlText,a);
QuerySetCell(xmlqry,"descr",xmlfile.Tutorials.TutorialID[a].Description.xmlText,a);
QuerySetCell(xmlqry,"id",xmlfile.Tutorials.TutorialID[a].xmlAttributes.id,a);
//xmlfile.Tutorials.TutorialID[a].xmlAttributes.id gets the value of the attribute in the tag
}
</cfscript>
<cfquery name="tutorials" dbtype="query">
SELECT *
FROM xmlqry
ORDER BY id DESC
</cfquery>
<cfoutput query="tutorials">
<a href="#url#" target="_blank">#title#</a> - #descr#
<br />
--Author: #author#
<br /><br />
</cfoutput>
Questions? Comments? Need Help? Contact Drew Tempelmeyer.About This Tutorial
Author: Drew Tempelmeyer
Total Views: 172,129
Submission Date: November 09, 2003
Last Update Date: August 25, 2011
All Tutorials By This Autor: 1
Skill Level: Intermediate
Platforms Tested: CFMX,CF8,CF9,BlueDragon,RailoTotal Views: 172,129
Submission Date: November 09, 2003
Last Update Date: August 25, 2011
All Tutorials By This Autor: 1
Discuss This Tutorial
-
-
What was the solution to the XML-SQL Problem? is there an easy way to do it? Has anyone here used SQLXML? is there a clean way to do it in CF?
-
I'd like to see that solution. I have an XML from ebay that i want to dump into a mySQL database. I don't know anything about XML. help.
-
Can you send me the XML file. The URL seems to be out dated
-
I am trying to take an XML file into SQL so that I can run queries on it. We collect stats from one company, put the data into our database and run reports off it. The reports have several calculations so I don't feel that running xml queries is the best method. The data file is pure XML. I am not sure how to get into a format that can be insert into a table? can anyone help?
-
4.5 doesn't support query of queries so the dbtype of query is what's breaking it for you...
-
I tried using this tutorial with CF 4.5 and got a
error becuase no datasource was declared. So I put it on our Web server, which is running 5.0. Can XML be parsed using this version? Pat -
little correction :
SELECT * FROM xmlqry ORDER BY eventid asc should beSELECT * FROM xmlqry ORDER BY id desc -
thanks for the example, I hadn't used xml in cf yet, and I had to retrieve 3rd party content as you explained in last post. I have simplified your code a bit to avoid headaches :)) I put it here in case someone would reuse your example:
xmlfile = xmlparse(cfhttp.filecontent); //Parses the XML xmlsize = arraylen(xmlfile.Tutorials.xmlchildren); //Tutorials is the parent tree xmlqry = QueryNew(#fieldsList#); //Sets a query for output QueryAddRow(xmlqry,xmlsize); for(a=1;a LTE xmlsize;a=a+1) { for(b=1;b LT listLen(fieldsList);b=b+1) { QuerySetCell(xmlqry,listGetAt(fieldsList, b),xmlfile.Tutorials.TutorialID[a][listGetAt(fieldsList, b)].xmlText,a); } } SELECT * FROM xmlqry ORDER BY eventid asc #zzz# #evaluate(zzz)#
-
XML should be used when you want to deliver information to third-party easily... Take into account the Tutorial Feeds on this site: http://www.easycfm.com/syndication/EasyFeed.xml They allow people who run ColdFusion related sites to display tutorials on their site easily... You can also keep information in a temporary XML structure that will contain specific information about a logged in user... Truth be told.. the possibilities are pretty much endless. XML was not created to replace the database.. it was created to allow the exchange of information to be easier.. since all XML ends up being processed the same, etc.. so it's a standard that is uses to create and parse data between two systems!
Advertisement
