Using XML and ColdFusion queries

You can convert XML documents into ColdFusion query objects and manipulate them using queries of queries. This technique does not require the use of XPath and provides a method of searching XML documents and extracting data that is natural to ColdFusion programmers.

Converting XML to a ColdFusion query

The following example reads an XML document, converts it to a query object, and then performs a query of queries on the object to extract selected data:

<!--- Read the file and convert it to an XML document object ---> 
<cffile action="read" file="C:\CFusionMX7\wwwroot\myexamples\employees.xml" variable="myxml">
<cfset mydoc = XmlParse(myxml)>

<!--- get an array of employees --->
<cfset emp = mydoc.employee.XmlChildren>
<cfset size = ArrayLen(emp)>

<cfoutput>
Number of employees = #size#
<br>
</cfoutput>
<br>
<!--- create a query object with the employee data --->
<cfset myquery = QueryNew("fname, lname") >
<cfset temp = QueryAddRow(myquery, #size#)>
<cfloop index="i" from = "1" to = #size#>
   <cfset temp = QuerySetCell(myquery, "fname", 
      #mydoc.employee.name[i].first.XmlText#, #i#)>
   <cfset temp = QuerySetCell(myquery, "lname", 
      #mydoc.employee.name[i].last.XmlText#, #i#)>
</cfloop>

<!--- Dump the query object --->
Contents of the myquery Query object: <br>
<cfdump var=#myquery#>
<br><br>

<!--- Select entries with the last name starting with A and dump the result --->
<cfquery name="ImqTest" dbType="query">
   SELECT lname, fname 
   FROM myquery 
   WHERE lname LIKE 'A%'
</cfquery>
<cfdump var=#imqtest#>

Converting a query object to XML

The following example shows how to convert a query object to XML. It uses cfquery to get a list of employees from the cfdocexamples database and saves the information as an XML document.

<!--- Query the database and get the names in the employee table --->
<cfquery name="myQuery" datasource="cfdocexamples">
   SELECT FirstName, LastName
   FROM employee 
</cfquery>

<!--- Create an XML document object containing the data --->
<cfxml variable="mydoc">
   <employee>
      <cfoutput query="myQuery">
          <name>
            <first>#FirstName#</first>
            <last>#LastName#</last>
         </name>
      </cfoutput>
   </employee>
</cfxml>

<!--- dump the resulting XML document object --->
<cfdump var=#mydoc#>
<!--- Write the XML to a file --->
<cffile action="write" file="C:\inetpub\wwwroot\xml\employee.xml"
      output=#toString(mydoc)#>

View comments in LiveDocs