FetchXML for SQL developers

select * from TAB

The most basic SQL statement …

select * from TAB

… is formulated in FetchXML like so:

<fetch mapping="logical" version="1.0">
  <entity name="tab">
    <all-attributes />
  </entity>
</fetch>

Select specific attributes

Specific attributes, such as col_1, col_2 and col_3 in the following example …

select
   col_1,
   col_2,
   col_3
from
   tab

… are specified with the <attribute> tag:

<fetch mapping="logical" version="1.0">
  <entity name="tab">
    <attribute name="col_1" />
    <attribute name="col_2" />
    <attribute name="col_3" />
  </entity>
</fetch>

Where conditions

A where condition …

select *
from
   TAB
where
   id = 5

requires a <filter> and a <condition> tag. The operator attribute specifies the kind of comparison.

<fetch mapping="logical" version="1.0">
  <entity name="tab">
    <all-attributes />
    <filter>
      <condition attribute="id" operator="eq" value="5" />
    </filter>
  </entity>
</fetch>

Similiarly, a greater than condition (where id > 5) requires the value of the operator attribute to be gt:

<fetch mapping="logical" version="1.0">
  <entity name="tab">
    <all-attributes />
    <filter>
      <condition attribute="id" operator="gt" value="5" />
    </filter>
  </entity>
</fetch>

in

A where id in (2, 3, 5, 7, 11) condition lists the values that must be satifised in a series of <value> tags:

<fetch mapping="logical" version="1.0">
  <entity name="tab">
    <all-attributes />
    <filter>
      <condition attribute="id" operator="in">
        <value>2</value>
        <value>3</value>
        <value>5</value>
        <value>7</value>
        <value>11</value>
      </condition>
    </filter>
  </entity>
</fetch>

LIKE

A query that uses like and percent signs …

select *
from
   tab
where
   col_1 like '%foo%'

<fetch mapping="logical" version="1.0">
  <entity name="tab">
    <all-attributes />
    <filter>
      <condition attribute="col_1" operator="like" value="%foo%" />
    </filter>
  </entity>
</fetch>

Note: when transmitting such a query over HTTP, the % and the text between them seems to be needed to be encoded with URL (aka percent) encoding.

IS NOT NULL

A is not null filter can be set with equally named operator:

<fetch  …
  <filter>
    <condition attribute="im_originatinglocalpolicy" operator="not-null" />
  </filter>
</fetch>

select top 10 …

TOP n queries (select top 10 from TAB …) are specified using the count attribute in the <fetch> tag:

<fetch mapping="logical" count="10" version="1.0">
  <entity name="tab">
    <all-attributes />
    <filter>
      <condition attribute="id" operator="gt" value="5" />
    </filter>
  </entity>
</fetch>

Joining tables

While SQL uses the join keyword to join tables …

select
   P.text,
   C.val
from
   par P                                join
   chl  C on P.id = C.parent_id

… FetchXML uses <link-entity>:

<fetch mapping="logical" version="1.0">
  <entity name="par">

    <attribute name="text" />

    <link-entity name="chl" from="parent_id" to="id" alias="C" link-type="inner">
        <attribute name="val" />
    </link-entity>

  </entity>
</fetch>

In v8.x versions of Web API, the value of the linked entity 'val' will be returend as C_x002e_val (hex 002e is Unicode for full stop).

This behavior is not present anymore in v9.x releases.

The following example tries to demonstrate how a join with conditions …

select
   P.text,
   C.val
from
   par as P join chl as C on P.id = C.parent_id
where
   P.id  = 42        and
   C.val > 999

… is translated to a FetchXML query:

<fetch mapping="logical" version="1.0">
  <entity name="par">

    <attribute name="text" />

    <filter>
          <condition attribute="id" operator="eq" value="42" />
    </filter>

    <link-entity name="chl" from="parent_id" to="id" alias="C" link-type="inner">

         <attribute name="val" />

         <filter>
            <condition attribute="val" operator="gt" value="999" />
         </filter>
    </link-entity>

  </entity>
</fetch>

A FetchXML query has a limit of a maximum of 10 linked entities!

order by

A result set is returned in a specific order using the order by clause in SQL …

select
   col_1,
   col_2,
   col_3
from
   tab
order by
   col_1,
   col_2 desc

… and using <order> tags in FetchXML:

<fetch mapping="logical" version="1.0">
  <entity name="tab">
    <attribute name="col_1" />
    <attribute name="col_2" />
    <attribute name="col_3" />
    <order attribute="col_1" />
    <order attribute="col_2" descending="true" />
  </entity>
</fetch>

order by in a join

select
   t1.id,
   t1.col_1,
   t1.col_2,
   t2.id,
   t2.col_a,
   t2.col_b
from
   tab_one  t1                                   join
   tab_two  t2 on t1.id = t2.id_tab_one
order by
   t1.id,
   t2.id

<fetch mapping="logical" version="1.0">
  <entity name="tab_one">
    <attribute name="id" />
    <attribute name="col_1" />
    <attribute name="col_2" />
    <order attribute="id" />
    <link-entity name="tab_two" from="id_tab_one" to="id" alias="t2" link-type="inner">
      <attribute name="id" />
      <attribute name="col_a" />
      <attribute name="col_b" />
      <order attribute="id" />
    </link-entity>
  </entity>
</fetch>

select
   t1.id,
   t1.col_1,
   t1.col_2,
   t2.id,
   t2.col_a,
   t2.col_b
from
   tab_one  t1                                   join
   tab_two  t2 on t1.id = t2.id_tab_one
order by
   t2.id,   -- Note the order here
   t1.id

<fetch mapping="logical" version="1.0">
  <entity name="tab_one">
    <attribute name="id" />
    <attribute name="col_1" />
    <attribute name="col_2" />
    <order attribute="id" />
    <link-entity name="tab_two" from="id_tab_one" to="id" alias="t2" link-type="inner">
      <attribute name="id" />
      <attribute name="col_a" />
      <attribute name="col_b" />
      <order attribute="id" />     <!--  This looks very fishy -->
    </link-entity>
  </entity>
</fetch>

Mark Carrington makes the following important observation regarding ordering a result set that involves multiple tables:

Bear in mind there is no way to specify the sequence that orderings are applied across multiple entities. Orders from the main <entity> are always applied first, then the first <link-entity>, then any sub-link-entities, and so on. In this example:

He further notes that the result set is sorted according to the primary name attribute of the related entity.

«Older than» operator

<fetch>
  <entity name="incident">
   <attribute name="title" />
   <attribute name="ticketnumber" />
   <attribute name="createdon" />
   <attribute name="incidentid" />

   <filter type="and">
     <condition attribute="createdon" operator="olderthan-x-minutes" value="40" />
   </filter>

  </entity>
</fetch>

Links

http://sql2fetchxml.com/