I was also hoping to find a piece of code to convert CAML to SQL in order to build my own SQL Statement to access the data.
My main project is to build a SharePoint ribbon extension to export Lists (Internal and External) content to CSV and in the case of external lists, to be able to bypass the throttling limit (2000) imposed in External Content Types (BCS).
I'm using the information in the metada store and the secure store to build the connection string and access the Database directly.
When I needed to improve my code to include filters, I ended up building my own methods to get the "Where" part of a View Query and convert it to a SQL-Like Where statement:
Input:
In my case it's a SPView object but it can easily be converted to use a string instead. I'm extracting the "CAML" like query from it.
"<Where>
<And>
<Or>
<Geq>
<FieldRef Name=\"Microfilm\" />
<Value Type=\"Text\">10</Value>
</Geq>
<Leq>
<FieldRef Name=\"Microfilm\" />
<Value Type=\"Text\">50</Value>
</Leq>
</Or>
<BeginsWith>
<FieldRef Name=\"Title\" />
<Value Type=\"Text\">Ice</Value>
</BeginsWith>
</And>
</Where>"
Ouput:
"(Microfilm >= 10 OR Microfilm <= 50) AND Title LIKE 'Ice%'"
Here are the methods:
This method will extract the "Where" node from a view Query and
pass it to a method to process it and return a SQL like statement.
private static string ViewQueryToSqlWhere(SPView v)
{
string sqlWhere = string.Empty;
XmlDocument xmlDoc = new XmlDocument();
XmlNodeList nodeList;
//Add <Query> around the SPView.Query since a valid XML document requires a single root element.
//and SPView.Query doesn't.
xmlDoc.LoadXml("<Query>" + v.Query + "</Query>");
nodeList = xmlDoc.GetElementsByTagName("Where");
if (nodeList.Count == 1)
{
XmlNode nodeWhere = nodeList[0];
if (nodeWhere.HasChildNodes) //Should Always be the case
{
StringBuilder sb = new StringBuilder();
bool isSuccess = ProcessWhereNode(nodeWhere, ref sb);
}
}
return sqlWhere;
}
This method will call another method to recursively go through all the nodes to get the values and operators within the View Query "Where" node. It will put round bracket around "OR" statements to conserve the operation priority.
private static bool ProcessWhereNode(XmlNode xmlNode, ref StringBuilder sb)
{
bool isSuccess = false;
Stack<string> operatorStack = new Stack<string>();
Queue<string> valueQueue = new Queue<string>();
string previousOp = string.Empty;
string strOperator = string.Empty;
try
{
//Call a method to iterate "recursively" throught the nodes to get the values and operators.
if (ProcessRecursiveWhereNode(xmlNode, "", "", ref operatorStack, ref valueQueue))
{
while (valueQueue.Count > 0)
{
if (operatorStack.Count > 0)
{
strOperator = operatorStack.Pop();
//Open bracket if it's an OR operator except if the previous one was also an OR.
if (strOperator == "OR" && previousOp != "OR")
sb.Append("(");
}
else
{
strOperator = string.Empty;
}
sb.Append(valueQueue.Dequeue());
//Close bracket if previous OP was an OR, and it's not followed by another one
if (previousOp == "OR" && strOperator != "OR")
sb.Append(")");
if (strOperator != string.Empty)
{
sb.Append(" " + strOperator + " ");
}
previousOp = strOperator;
}
}
}
catch (Exception ex)
{ }
return isSuccess;
}
This method does most of the work to go throught an iterate each nodes:
private static bool ProcessRecursiveWhereNode(XmlNode xmlNode, string strOperatorValue, string strOperatorType, ref Stack<string> operatorStack, ref Queue<string> valueQueue)
{
bool isSuccess = false;
string fieldName = string.Empty;
string value = string.Empty;
string thisIterationOperatorType = string.Empty;
string thisIterationOperatorValue = string.Empty;
try
{
XmlNodeList nodeList = xmlNode.ChildNodes;
//Get Child node - Possible tags {<Or>, <And>, <Eq>, <Neq>, <Gt>, <Lt>, <Geq>, <Leq>, </BeginsWith>, <Contains>, <FieldRef>, <Value>}
foreach (XmlNode node in nodeList)
{
thisIterationOperatorType = string.Empty;
thisIterationOperatorValue = string.Empty;
//Check if it's one of these tag: <Or>, <And>, <Eq>, <Neq>, <Gt>, <Lt>, <Geq>, <Leq>, </BeginsWith>, <Contains>
thisIterationOperatorValue = GetOperatorString(node.Name, out thisIterationOperatorType);
if (thisIterationOperatorType == "statement")
operatorStack.Push(thisIterationOperatorValue);
//It's one of these tag: <Or>, <And>, <Eq>, <Neq>, <Gt>, <Lt>, <Geq>, <Leq>, </BeginsWith>, <Contains>
if (thisIterationOperatorValue != string.Empty)
{
ProcessRecursiveWhereNode(node, thisIterationOperatorValue, thisIterationOperatorType, ref operatorStack, ref valueQueue);
}
else //It is probably a <FieldRef> or <Value> tag.
{
if (node.Name == "FieldRef")
fieldName = node.Attributes["Name"].Value.ToString();
else if (node.Name == "Value")
value = node.LastChild.Value.ToString();
}
}
if (strOperatorType == "value" && strOperatorValue != string.Empty && fieldName != string.Empty && value != string.Empty)
{
valueQueue.Enqueue(string.Format(strOperatorValue, fieldName, "'" + value + "'"));
}
isSuccess = true;
}
catch
{
isSuccess = false;
throw;
}
return isSuccess;
}
This last methods could probably been included in the recursive one but in my first iteration of building the code it made more sense to make a separate one and I kept it this way.
It simply gets some information on the operators and associates an operator string which will be used to construct the individual pieces of the SQL Where Statement.
static private string GetOperatorString(string tagName, out string operatorType)
{
string operatorString = string.Empty;
switch (tagName)
{
case "Or":
operatorString = "OR";
operatorType = "statement";
break;
case "And":
operatorString = "AND";
operatorType = "statement";
break;
case "Eq":
operatorString = "{0} = {1}";
operatorType = "value";
break;
case "Neq":
operatorString = "{0} != {1}";
operatorType = "value";
break;
case "Gt":
operatorString = "{0} > {1}";
operatorType = "value";
break;
case "Lt":
operatorString = "{0} < {1}";
operatorType = "value";
break;
case "Geq":
operatorString = "{0} >= {1}";
operatorType = "value";
break;
case "Leq":
operatorString = "{0} <= {1}";
operatorType = "value";
break;
case "BeginsWith":
operatorString = "{0} LIKE '{1}%";
operatorType = "value";
break;
case "Contains":
operatorString = "{0} LIKE '%{1}%";
operatorType = "value";
break;
default:
operatorString = string.Empty;
operatorType = string.Empty;
break;
}
return operatorString;
}
I know it's not a full conversion tool but it's a start and for now it fits my need. I hope this will help someone and save them some valuable time.
