I want to split an attribute value based on a separator e.g '|' using FetchXML query. I have tried to use sql2fetch tool as well but was unable to do this as FetchXML does not support some sql keywords.
You cannot do that using FetchXML. In the end FetchXML is converted by the Dynamics CRM platform to T-SQL, but it only supports a basic set of capabilities T-SQL offers. One of its limitations is that you cannot transform field values.