CF_XMLQuery : Tag Reference

CF_XMLQuery

Returns a standard ColdFusion query from an XML query on an XML document.

Syntax

<CF_XMLQUERY NAME="query_name"
             DATASOURCE="XMLDatasourceObject"
             TYPE="XPATH/XQuery/SQL"
             SORTCOLUMN="sort_column_name"
             SORTDIRECTION="asc/desc"
             SORTTYPE="numeric/text/textnocase"
             bTHROWONEMPTY="NO"
             CACHEDWITHIN="timespan"> 

Query Statement

</CF_XMLQUERY>

NAME

Required. The name you assign to the query. Query names must begin with a letter and may consist of letters, numbers, and the underscore character (spaces are not allowed). The query name is used later in the page to reference the record set.

DATASOURCE

Required. The XMLDatasource object to run the query against.

QUERYTYPE

Optional. The type of the supplied query.

XPath -- (DEFAULT) the query statement is an XPath query
XQuery -- The query statement is an XQuery query. Until the XQuery spec if finished, this option will throw and exception.
SQL -- The query statement in SQL format (Only CF5+)

SORTCOLUMN

Optional. By supplying a SortColumn, it siginifies that the resulting recordset should be sorted, by the supplied column name.

SORTDIRECTION

Optional. If the SortColumn attribute is given, this attributes specifies the order of the sort:

asc -- (DEFAULT) ascending sort order
desc -- decending sort order

SORTTYPE

Optional. If the SortColumn attribute is given, this attribute specifies the type of sort to be executed.

numeric -- Sorts numerically
text -- (DEFAULT) Sorts text alphabetically, uppercase before lowercase
textnocase -- Sorts text alphabetically; case is ignored

bTHROWONEMPTY

Optional. If no nodes are found for the specified query, this attribute tells the tag what action to take. If this is set to no (Default), the tag returns an empty record set. If set to yes, the tag will throw an exception of type XMLQuery.EmptyRecordset.

CACHEDWITHIN

Optional. Enter a timespan using the ColdFusion CreateTimeSpan function. Cached query data will be used if the original query date falls within the time span you define. The CreateTimeSpan function is used to define a period of time from the present backwards. To use cached data, the current query must use the same statement, and datasource name.

Usage

The columns returned in the ColdFusion query, depend upon the attributes and children of the matching XML nodes. The following example shows the type of column names, assuming the following nodes where returned from the XPath query.

<person gender="male" age="27">David</person>
<person gender="male" age="29" hobby="music">Tom</person>
<person gender="male" age="21" hobby="football">0lly</person>

We would get the following column names returned :

people -- containing 'David'
people_gender -- containing 'male'
people_age -- containing '27'

Another way to query the XMLDatasource is to use an SQL statement. This is the same as doing a select all nodes XPath, and then doing a query of queries on it. However doing it in one statement makes much neater code.

When doing SQL queries you should note that the name of the table in the FROM part of the select should be the same as the name of the datasource. For example if the call to XMLQuery was <CF_XMLQuery Datasource="myData"> the name of the table would also be myData, i.e. SELECT * FROM myData. If it isn't the same, an exception will be generated.

As an example of an SQL query, imagine we wanted to find all people under 29 in the above XML. The SQL statement for this would like like:

<CF_XMLQuery Datasource="MyPeople" Type="SQL">

  SELECT * 
  FROM MyPeople
  WHERE person_age < 29
</CF_XMLQuery>

Example


<CFPARAM Name="URL.Company" Default="Torchbox">

<!--- Display a quick form --->
<Form Method="Get">
   <Select Name="Company">
   	<Option Value="Torchbox">Torchbox</Option>
   	<Option Value="WildFusion">WildFusion</Option> 
   </Select> 
   <input type="submit" value="Show Employees">
</Form>


<!--- Create a datasource --->
<CF_XMLDatasource Name="MyData">
   <people>
   	<person age="27" company="Torchbox">Tom Dyson</person>
   	<person age="27" company="WildFusion">David Maddison</person>    
   	<person age="23" company="WildFusion">Peter Piper</person>
   	<person age="29" company="Torchbox">olly</person>    
   </people>
</CF_XMLDatasource>

<!--- Query the XML for all employees of Torchbox --->
<CF_XMLQuery Datasource="MyData" Name="qryTorchbox">
   //person[@company='#URL.Company#']
</CF_XMLQuery>



<!--- Same query done using SQL 
<CF_XMLQuery Datasource="MyData" Name="qryTorchbox" Type="SQL">
  SELECT person, person_age
  FROM MyData
  WHERE person_company = '#URL.Company#'
</CF_XMLQuery>
--->

<!--- Display the record count --->
<CFOUTPUT>#qryTorchbox.RecordCount# Employees of #URL.Company# found:<br></CFOUTPUT>


<!--- Display the names --->
<CFOUTPUT Query="qryTorchbox">
	#person# (#person_age#)<br>
</CFOUTPUT>


Produced by Tom Dyson of Torchbox, and David Maddison of Wildfusion

ColdFusion XML Toolkit
Tag Reference : CF_XMLQuery