Fetch XML Schema
The following is the schema for the fetch XML parameter used in formulating queries against the Microsoft CRM database.
Included in this section:
- Fetch XML Schema in BNF
- Fetch XML Schema in XSD
- More information about conditions
- Examples showing how to use Fetch XML
BNF
The following describes the schema in Backus-Naur form (BNF):
FetchXml :=
<fetch
(page='page-number')? // default is 0
(count='items-per-page')? // default is unbounder
(utc-offset='utc-offset')? // default is 0
(mapping= ('internal' | 'logical' | 'physical') )? // default is logical
(aggregate = ('true' | 'false' | '1' | '0') )? // '1' and '0' not currently supported. default is false
>
<entity name='entity-name'>
Entity-Xml
</entity>
</fetch>
Entity-Xml :=
( <all-attributes /> | <no-attributes /> | Attribute-Xml* )
( Filter-Xml |
LinkEntity-Xml |
Order-Xml )*
LinkEntity-Xml :=
<link-entity
name = 'entity-name'
(to = 'column-name')? // the column on the remote entity
(from = 'column-name')? // the column on the local entity
(alias = 'table-alias')?
(link-type = ('natural' | 'inner' | 'outer'))?
>
Entity-Xml
</link-entity>
Order-Xml :=
<order
attribute='attr-name'
(descending= ('true' | 'false' | '1' | '0')?
/>
Attribute-Xml :=
<attribute
name='attr-name'
(aggregate=('count') alias='alias-name')?
/>
Filter-Xml :=
<filter
(type= ('or' | 'and'))? // default is 'and'
>
( Condition-Xml | Filter-Xml )*
</filter>
Condition-Xml :=
Condition-None | // For operators like 'not-null', where there is no explicit value
Condition-Single | // For operators that take an attribute and a value, 'eq', for example
Condition-Multiple // for operators that involve collections -- 'in' or 'not-in' for example
Condition-None :=
<condition attribute='attribute-name' operator=
(
'null' |
'not-null' |
'yesterday' |
'today' |
'tomorrow' |
'next-seven-days' |
'last-seven-days' |
'next-week' |
'last-week' |
'this-week' |
'this-month' |
'last-month' |
'next-month' |
'this-year' |
'last-year' |
'next-year' |
'eq-userid' |
'ne-userid' |
'eq-businessid' |
'ne-businessid'
)
/>
Condition-Single :=
<condition attribute='attribute-name' operator=
(
'eq' |
'ne' |
'lt' |
'gt' |
'le' |
'ge' |
'like' |
'not-like' |
'on' |
'on-or-before' |
'on-or-after')
/>
condition-Multiple :=
<condition attribute='attribute-name' operator=
(
'in' |
'not-in' |
'between' |
'not-between' )
Value-Xml *
</condition>
Value-Xml :=
<value>text</value>
XSD
The following describes the schema in XML Schema definition language (XSD) form:
<?xml version='1.0' ?>
<Schema name='fetch-schema' xmlns='urn:schemas-microsoft-com:xml-data' xmlns:dt='urn:schemas-microsoft-com:datatypes'>
<!--
condition element - used for capturing entity and link-entity
"where" clause criteria
-->
<ElementType name='value' content='textOnly' model='closed' />
<ElementType name='condition' content='eltOnly' model='closed'>
<AttributeType name='attribute' dt:type='string' required='yes' />
<AttributeType name='operator' dt:type='enumeration' dt:values='lt gt le ge eq ne null not-null in not-in between not-between like not-like yesterday today tomorrow next-seven-days last-seven-days next-week last-week this-month last-month next-month on on-or-before on-or-after this-year last-year next-year eq-userid ne-userid eq-businessid ne-businessid' required='yes' default='eq' />
<AttributeType name='value' dt:type='string' required='no' />
<AttributeType name='aggregate' dt:type='enumeration' dt:values='count' required='no' />
<AttributeType name='alias' dt:type='string' required='no' />
<!-- -->
<attribute type='attribute' />
<attribute type='operator' />
<!--
The attribute "value" is used for all operators that need to compare against a single value (eg. eq).
The element "value" is used for operators that need to compare against multiple values (eg. in).
Some operators require neither the attribute "value" or the element "value" (eg. null).
-->
<attribute type='value' />
<attribute type='aggregate' />
<attribute type='alias' />
<element type='value' minOccurs='0' maxOccurs='*' />
</ElementType>
<!--
filter element - used for constructing complex conditionals
legal one entity and link-entity
-->
<ElementType name='filter' content='eltOnly' model='closed'>
<AttributeType name='type' dt:type='enumeration' dt:values='and or' required='no' default='and' />
<!-- -->
<attribute type='type' />
<element type='condition' minOccurs='0' maxOccurs='*' />
<element type='filter' minOccurs='0' maxOccurs='*' />
</ElementType>
<!--
attribute elements - used for selecting attributes from the
surrounding entity / link-entity, these
values are returned as part of the fetch
-->
<ElementType name='all-attributes' content='empty' model='closed' />
<ElementType name='attribute' content='empty' model='closed'>
<AttributeType name='name' dt:type='string' required='yes' />
<!-- -->
<attribute type='name' />
</ElementType>
<!--
order element - used to specify a sort order
-->
<ElementType name='order' content='eltOnly' model='closed'>
<AttributeType name='attribute' dt:type='string' required='yes' />
<AttributeType name='descending' dt:type='boolean' required='no' default='false' />
<!-- -->
<attribute type='attribute' />
<attribute type='descending' />
</ElementType>
<!--
link-entity element - used for joining one entity to it's "parent"
-->
<ElementType name='link-entity' content='eltOnly' model='closed' order='many'>
<AttributeType name='to' dt:type='string' required='no' />
<AttributeType name='from' dt:type='string' required='no' />
<AttributeType name='name' dt:type='string' required='yes' />
<AttributeType name='alias' dt:type='string' required='no' />
<AttributeType name='link-type' dt:type='string' dt:values='natural inner outer' required='no' />
<!-- -->
<attribute type='name' />
<attribute type='to' />
<attribute type='alias' />
<attribute type='link-type' />
<element type='all-attributes' minOccurs='0' maxOccurs='1' />
<element type='attribute' minOccurs='0' maxOccurs='*' />
<element type='order' minOccurs='0' maxOccurs='*' />
<element type='filter' minOccurs='0' maxOccurs='1' />
<element type='link-entity' />
</ElementType>
<!--
entity element - used for specifying the root element for a fetch, only
one root entity is allowed in a given fetch, all others
are dependent on this entity and are marked as
link-entity
-->
<ElementType name='entity' content='eltOnly' model='closed' order='many'>
<AttributeType name='name' dt:type='string' required='yes' />
<!-- -->
<attribute type='name' />
<element type='all-attributes' minOccurs='0' maxOccurs='1' />
<element type='attribute' minOccurs='0' maxOccurs='*' />
<element type='order' minOccurs='0' maxOccurs='*' />
<element type='link-entity' />
<element type='filter' minOccurs='0' maxOccurs='1' />
</ElementType>
<!--
fetch element - root element for the query
-->
<ElementType name='fetch' content='eltOnly' model='closed'>
<AttributeType name='count' dt:type='int' required='no' />
<AttributeType name='page' dt:type='int' required='no' />
<AttributeType name='utc-offset' dt:type='int' required='no' />
<AttributeType name='aggregate' dt:type='boolean' required='no' />
<AttributeType name='mapping' dt:type='enumeration' dt:values='internal logical physical' required='no' />
<!-- -->
<attribute type='count' />
<attribute type='page' />
<attribute type='utc-offset' />
<attribute type='aggregate' />
<attribute type='mapping' />
<element type='entity' minOccurs='1' maxOccurs='1' />
</ElementType>
</Schema>
Conditions
The following table provides more information regarding conditions in Fetch XML.
| Condition | Actual comparison | Value |
| equals x | eq | x |
| does not equal x | ne | x |
| is greater than x | gt | x |
| is greater than or equal to x | ge | x |
| is less than x | lt | x |
| is less than or equal to x | le | x |
| begins with x | like | x% |
| does not begin with x | not-like | x% |
| ends with x | like | %x |
| does not end with x | not-like | %x |
| contains x | like | %x% |
| does not contain x | not-like | %x% |
| exists | not-null | |
| does not exist | null | |
| anytime | not-null | |
| yesterday | yesterday | |
| today | today | |
| tomorrow | tomorrow | |
| in next 7 days | next-seven-days | |
| in last 7 days | last-seven-days | |
| next week | next-week | |
| last week | last-week | |
| this week | this-week | |
| this month | this-month | |
| last month | last-month | |
| next month | next-month | |
| this year | this-year | |
| next year | next-year | |
| last year | last-year | |
| on x | on | x |
| on or after x | on-or-after | x |
| on or before x | on-or-before | x |
| in between | between | |
| not between | not-between | |
| in | in | |
| not in | not-in | |
| equals user id | eq-userid | |
| does not equal user id | ne-userid | |
| equals business id | eq-businessid | |
| does not equal business id | ne-businessid |