Fetch XML Examples

CRM 1.0

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>

© 2005 Microsoft Corporation. All rights reserved.


Show: