Parsing XML As Query Object In Coldfusion

I work in the financial industry as a developer and notnames to relate the data to a structure like a
that the financial industry has any greaterdatabase.
implementation of XML than any other, but web<cfset myquery = QueryNew("cat, firstName,
applications that utilize back office processing for loanlastName, phone, email")>
applications, or new account opening processes, orNow that we have the "Columns" of the query object,
retail internet banking applications all to some extentwe use the Size object to give us the parameters to
make calls to a host. Many times it's a DB2 database,define the Rows like this.
and many times some other process that returns<cfset temp = QueryAddRow(myquery, #size#)>
account information or validation information in xmlNow you've established a Query Object 'temp'.
format.Now comes the part where you fill the Query Object
One of the fun things that I found working inwith the data from the XML document. Loop through
ColdFusion is the many ways you can manipulate xmlevery "row" in the object adding its value from the xml
data. I'm certain this type of process isn't exclusive toobject like an array.
ColdFusion, but I wanted to put it out there anyway.<cfloop index="i" from = "1" to = #size#>
Many developers have a little apprehension about<cfset temp = QuerySetCell(myquery, "cat",
working with new technologies at first, or trying, #i#)>
something that hasn't been already tried and true and<cfset temp = QuerySetCell(myquery, "firstName",
of course documented heavily. And frankly it's hard to#mydoc.phonebook.contact[i].firstName.XmlText#,
find the extra time with a heavy work load and#i#)>
deadlines to meet to go off experimenting But I<cfset temp = QuerySetCell(myquery, "lastName",
managed to put aside some time to look at parsing#mydoc.phonebook.contact[i].lastName.XmlText#, #i#)>
XML into an object that developers are all familiar with,<cfset temp = QuerySetCell(myquery, "phone",
the Query object. As a developer, writing SQL is#mydoc.phonebook.contact[i].phone.XmlText#, #i#)>
common place, so I figured maybe looking at a hybrid<cfset temp = QuerySetCell(myquery, "email",
approach to XML and SQL in ColdFusion, might prove#mydoc.phonebook.contact[i].email.XmlText#, #i#)>
to offer some benefit.</cfloop>
So I'll show an example of reading in a Phonebook xmlEssentially what you've just done is take the xml and
document. It will look like this: You can copy this andturn it into the same type of data structure you would
save it off as phonebook.xml if you like.have when you query a database.
<?xml version="1.0"?>The rest should look familiar to many ColdFusion
<phonebook>developers who use ColdFusion Components which is
<contact category="friend">a great practice.
<firstName>John</firstName>Call the query function using the CFINVOKE. This
<lastName>Smith</lastName>particular function is if you were to sort the phonebook
<phone>412-555-1212</phone>individuals by last name.
<email></email><cfinvoke component="pbook_meths"
</contact>method="sortLName" returnVariable="Result">
<contact category="friend"><cfinvokeargument name="q_obj"
<firstName>Jane</firstName>value="#myquery#">
<lastName>Smith</lastName></cfinvoke>
<phone>412-555-1212</phone>The output of the result from the function would be no
<email></email>different than that of a normal SQL query.
</contact><table border=1 width=500 align=center>
<contact category="enemy"><th>category</th><th>fist name</th><th>last
<firstName>Bob</firstName>name</th><th>phone</th><th>email</th>
<lastName>Jones</lastName><cfoutput query="Result">
<phone>412-555-1213</phone><tr>
<email></email><td>#cat#</td><td>#firstName#</td>
</contact><td>#lastName#</td> <td>#phone#</td>
<contact category="co-worker"><td>#email#</td>
<firstName>Bill</firstName></tr>
<lastName>Johnson</lastName></cfoutput>
<phone>412-555-1214</phone></table>
<email>bill.</email>The function being called would be stored in its own file
</contact>as components are in ColdFusion and would look like
<contact category="friend">this.
<firstName>Jack</firstName><cffunction name="sortLName" access="remote"
<lastName>Robinson</lastName>returnType="query">
<phone>412-555-1215</phone><cfargument name="q_obj" required="Yes" >
<email><cftry>
</contact><cfquery name="pbTest" dbType="query">
</phonebook>SELECT *
Reading in the xml is short and sweet simply by usingFROM arguments.q_objorder by lastName,cat
this little block of code.</cfquery>
<cfhttp url=" method="GET" resolveurl="No" ><<cfcatch type="Any">
cfhttp><P><cfoutput>#cfcatch.message#</cfoutput><
<cfset mydoc = XmlParse(CFHTTP.FileContent)>P>
Now you've got the xml object in a defined variable</cfcatch>
name called "mydoc".</cftry>
Next you set a variable to contain the child element<cfreturn pbTest>
nodes of the xml document and do the same to</cffunction>
define the size of the document, which you will see inYour component can contain numerous functions
a bit as to what it's used for.manipulating the xml query object just the same as
<cfset pb =you would a normal query from a database. You don't
<cfset size = ArrayLen(pb)>have the power that a relational database offers of
Now create a query object with the phonebook data.course, but if you're comfortable writing SQL, you may
As you can see we already know what the nodefind yourself hitting the ground running with working
properties and names are to create the myquerywith xml in ColdFusion.
object containing the xml "column names". I say columnThanks and Happy Coding.