QueryXML

IMPORTANT  The SOAP API entered a limited enhancement phase in Q4 2020. Access to version 1.6 will be deactivated.

This section describes the Autotask Web Services API QueryXML schema for building queries using the query() API call. This XML schema allows you to build complex queries utilizing multiple expressions and conditions.

NOTE   query() will return a maximum of 500 records at once, sorted by their id value. To query records over the 500 maximum for a given set of search criteria, repeat the query and filter by id value > the previous maximum id value retrieved.

Autotask Web Services API QueryXML requires well formed XML. It must adhere to the basic XML standards and XML special characters must be escaped. For a list of XML special characters, refer to XML special characters, below.

The following table indicates which expression-datatype sets are now supported and which will return an error.

  string numeric datetime boolean
Equals Y Y Y Y
Not Equal Y Y Y Y
Less Than Y Y Y X
Less Than Or Equals Y Y Y X
Greater Than Y Y Y X
Greater Than Or Equals Y Y Y X
Begins With Y X X X
Ends With Y X X X
Contains Y X X X
Is Null Y Y Y Y
Is Not Null Y Y Y Y
Is This Day X X Y X

Like

 Y X X X

Not Like

 Y X X X

QueryXML elements and attributes

The following table describes the Autotask Web Services API QueryXML elements.

Element Description Values (where appropriate)
<queryxml> The <queryxml> element is the root element. 
The child elements of the <queryxml> element are <entity> and <query> elements.		  
<entity> Defines the Autotask element being queried. Permissible <entity> values: The name of any entity exposed by the Web Services API
<query> Defines the criteria for the data you wish to retrieve.
The allowable child elements of the <query> element are <field> elements and <condition> elements.		  
<field> Defines a field to be queried.

You can query on multiple entity fields by specifying more than one <field> element.
 
Multiple <field> elements are always combined using logical AND.
The child elements of the <field> element include the field name as a text value and one or more <expression> elements.

To specify that a field is a UDF, you must add udf="true" within the field tag. You can only specify one UDF field per query.		  
<expression> The <expression> element defines the test to apply to the field.

You can include multiple expressions for a given field.
Multiple expressions within a single field will be combined using a logical OR.

Use the ‘op’ attribute of the <expression> element to specify the type of comparison you are testing for. The available op attribute values for an expression element are listed to the right.

The value for the comparison is specified as a text value between the start and end tags for the expression element. 

Example using Expression Element:
The following example would match all records where the firstname field begins with "F."

<field>FirstName
<expression op="beginswith">F</expression>
</field>		 Available op attribute values:

Equals
NotEqual
GreaterThan
LessThan
GreaterThanorEquals
LessThanOrEquals
BeginsWith
EndsWith
Contains
IsNotNull
IsNull
IsThisDay
Like
NotLike
For AccountName and Account Phone Number fields only:

normalizedequals (see details below)
<condition> The <condition> element allows you to define a set of criteria to be evaluated as a single expression. You may use it to specify an OR condition between different fields and also to build more complex criteria sets by combining and nesting multiple <condition> elements.

When combining multiple <condition> elements, you use the operator attribute to specify whether the expression will be evaluated as an AND or OR condition. The default value for the operator attribute is AND.

The operator attribute will be ignored for a <condition> element when it is the first element within its parent element. 		 

normalizedequals is now available as an expression operator attribute for the AccountName and Account PhoneNumber fields (<expression op="normalizedequals">). This expression will pull in query results without the exact character matching search criteria required by the standard "equals" op attribute. Normalized data is stored in a table in the database. The following information is removed from these fields when we store the normalized data:

Characters:

` ~ ! @ # $ % ^ & * ( ) _ - + = { [ } ] | \ : " ' < > . ? / ’ « € » Ð × Þ ð ÷ þ ° ² — ¡ ¿

Words:

An, And, Co, Company, Corp, Corporation, Inc, Incorporated, Llc, Llp, Ltd, Of, The.

IMPORTANT  The "normalizedequals" operator is currently only available for the account phone number, not contact phone numbers or phone, fax or mobile numbers associated with locations, resources etc.

QueryXML examples

When querying on a single field, the <query> element would look like the following:

SQL: 

firstname = ‘Joe’

QueryXML:> 

<queryxml>
  <entity>contact</entity>
	<query>
		<field>firstname
		<expression op="equals">Joe</expression>
		</field>
	</query>
</queryxml>

When querying on multiple fields, the <query> element would look like the following:

SQL: 

firstname = ‘Joe’ AND lastname = ‘Smith’


QueryXML: 

<queryxml>
	<entity>contact</entity>
		<query>
			<condition>
				<field>firstname
					<expressionop="equals">Joe</expression>
				</field>
			</condition>
			<condition>
				<field>lastname
					<expression op="equals">Smith</expression>
				</field>
			</condition>
		</query>
</queryxml>

To create an OR condition between two field elements, wrap each field element in a <condition> element, and specify operator="OR" attribute for the second condition element:

SQL: 

firstname = ‘Joe’ OR lastname = ‘Brown’

QueryXML: 

<queryxml>
	<entity>contact</entity>
		<query>
			<condition>
				<field>firstname
					<expressionop="equals">Joe</expression>
				</field>
			</condition>
			<condition>
				<condition operator="OR">
				<field>lastname
					<expression op="equals">Brown</expression>
				</field>
			</condition>
		</query>
</queryxml>

Use nested conditions to specify more complex search criteria:

SQL: 

(
	firstname = ‘Joe’
	OR (
	(firstname = ‘Larry’ and lastname = ‘Brown’)
	OR	
	(firstname = ‘Mary’ and lastname = ‘Smith’)
		)
)
AND city <> ‘Albany’

QueryXML: 

<queryxml>
	<entity>contact</entity>
	<query>
		<field>firstname
			<expression op="equals">Joe</expression>
		</field>
	<condition operator="OR">
			<condition>
			<field>firstname
				<expression op="equals">Larry</expression>
			</field>
		<field>lastname
			<expression op="equals">Brown</expression>
		</field>
			</condition>
	<condition operator="OR">
		<field>firstname
			<expression op="equals">Mary</expression>
		</field>
		<field>lastname
			<expression op="equals">Smith</expression>
		</field>
		</condition>
	</condition>
		<field>city
			<expression op="notequal">Albany</expression>
		</field>
	</query>
</queryxml>

XML special characters

XML predefines entity references for the following five for special characters that would otherwise be interpreted as part of markup language.

Character Name Character Reference Entity Reference Numeric Reference
Ampersand & &amp; &#38;
Left angle bracket < &lt; &#60;
Right angle bracket > &gt; &#62;
Straight quotation mark " &quot; &#34;
Apostrophe ' &apos; &#39;

In order to use any of these special characters in context other than markup, you must escape them using the specified Entity Reference.