Fetch XML Examples
The following are examples of use of the QueryXML parameter. See also Fetch XML Schema.
Example 1
Return all attributes of the account entity where the owning user's last name equals the value crmlastname, first name equals the value crmfirstname, and nickname equals the value crmnickname.
<fetch mapping='logical'>
<entity name='account'><all-attributes/>
<link-entity name='systemuser' to='owninguser'>
<filter type='and'>
<condition attribute = 'lastname' operator='eq'
value='crmlastname'/>
<condition attribute='firstname' operator='eq'
value='crmfirstname'/>
<condition attribute='nickname' operator='eq'
value='crmnickname'/>
</filter>
</link-entity>
</entity>
</fetch>
Example 2
Return all attributes from the account entity for accounts created today.
<fetch mapping='logical'>
<entity name='account'><all-attributes/>
<filter type='and'>
<condition attribute = 'createdon' operator='today'/>
</filter>
</entity>
</fetch>
Example 3
Return name and accountid from all accounts created within the last seven days.
<fetch mapping='logical'>
<entity name='account'>
<attribute name = 'name'/>
<attribute name = 'accountid'/>
<filter type='and'>
<condition attribute = 'createdon' operator='last-seven-days'/>
</filter>
</entity>
</fetch>"
Example 4
Return contactid and birthdate from all contacts where the birth date is December 12, 2000 (time format YYYY-MM-DDTHH:MM:SSZ).
<fetch mapping='logical'>
<entity name='contact'>
<attribute name = 'contactid'/>
<attribute name = 'birthdate'/>
<filter type='and'>
<condition attribute = 'birthdate' operator='on'
value='2000-12-12T00:00:00Z'/>
</filter>
</entity>
</fetch>
Example 5
Return all attributes from accounts where the account name either starts with "a" or starts with "d".
<fetch mapping='logical'>
<entity name='account'><all-attributes/>
<filter type='or'>
<condition attribute = 'name' operator='like' value='a%'/>
<condition attribute = 'name' operator='like' value='d%'/>
</filter>
</entity>
</fetch>
Example 6
Return name and accountid from all accounts where the name starts with either "a" or "x", or the name ends with "!".
<fetch mapping='logical'>
<entity name='account'>
<attribute name = 'name'/>
<attribute name = 'accountid'/>
<filter type='or'>
<condition attribute = 'name' operator='like' value='a%'/>
<condition attribute = 'name' operator='like' value='x%'/>
<condition attribute = 'name' operator='like' value='%!'/>
</filter>
</entity>
</fetch>
Example 7
Retrieve the ticker symbol of the account whose name is "My Account".
<fetch mapping='logical'>
<entity name='account'>
<attribute name='tickersymbol' />
<filter type='and'>
<condition attribute='name' operator='eq' value='My Account' />
</filter>
</entity>
</fetch>
Example 8
Retrieve all leads that were created within the last seven days.
<fetch mapping='logical'>
<entity name='lead'>
<all-attributes />
<filter type='and'>
<condition attribute='createdon' operator='last-seven-days' />
</filter>
</entity>
</fetch>
Example 9
Retrieve all contacts whose first name begins with "A" or last name ends with "Z". Sort the result set in ascending order by last name.
<fetch mapping='logical'>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter type='or'>
<condition attribute='firstname' operator='like' value='A%' />
<condition attribute='lastname' operator='like' value='%Z' />
</filter>
<order attribute='lastname' descending='false' />
</entity>
</fetch>
Example 10
Retrieve the number of users in the system.
<fetch mapping='logical' aggregate='true'>
<entity name='systemuser'>
<attribute name='systemuserid' aggregate='count' alias='number' />
</entity>
</fetch>
Example 11
Retrieve the first 10 account records created by user Jeff Smith in the last month.
<fetch mapping='logical' page='1' count='10'>
<entity name='account'>
<all-attributes />
<link-entity name='systemuser' to='createdby'>
<filter type='and'>
<condition attribute='firstname'
operator='eq' value='Jeff' />
<condition attribute='lastname'
operator='eq' value='Smith' />
</filter>
</link-entity>
<filter type='and'>
<condition attribute='createdon' operator='last-month' />
</filter>
</entity>
</fetch>
Example 12 – Inner Joins Between Entities
Retrieves the name field on the Invoice entity. This query returns a maximum of one (1) record that matches criteria on Page 1 of the result set.
<fetch mapping='logical' page='1' count='1'>
<entity name='invoice'>
<attribute name = 'name'/>
<link-entity name='invoicedetail' to='invoiceid' from='invoiceid'>
</link-entity>
<filter type='and'>
<condition attribute = 'accountid' operator='eq'
value='{7F901912-DD67-47B9-A5B3-B702B9F84680}'/>
</filter>
<link-entity name='account' to='accountid'>
</link-entity>
<filter type='and'>
<condition attribute = 'name' operator='like' value='%order%'/>
</filter>
</entity>
</fetch>
Example 13 – Inner Joins Between Entities
Retrieves the name field on the Invoice entity. This query returns a maximum of 10 records that match the criteria on Page 1 of the result set.
<fetch mapping='logical' page='1' count='10'>
<entity name='invoice'>
<attribute name = 'name'/>
<link-entity name='invoicedetail' to='invoiceid' from='invoiceid'>
</link-entity>
<filter type='and'>
<condition attribute = 'accountid' operator='eq'
value='{7F901912-DD67-47B9-A5B3-B702B9F84680}'/>
</filter>
<link-entity name='account' to='accountid'>
<filter type='and'>
<condition attribute = 'name' operator='like'
value='%Account%'/>
</filter>
</link-entity>
</entity>
</fetch>
Example 14 – Using the OrderBy Clause
Retrieves all contacts whose first name begins with "A" or last name ends with "Z" and sorts the result set in ascending order by last name.
<fetch mapping='logical'>
<entity name='contact'>
<attribute name='firstname' />
<attribute name='lastname' />
<filter type='or'>
<condition attribute='firstname' operator='like' value='A%' />
<condition attribute='lastname' operator='like' value='%Z' />
</filter>
<order attribute='lastname' descending='false' />
</entity>
</fetch>