Archive

Archive for the ‘XML’ Category

How To: Create CAML Query Dynamically in SharePoint

December 26, 2013 12 comments

In one of my project, I was having requirement where we must need to create CAML query dynamically.

So I’ve tried to googling..and i found some good options but it does not fulfill my requirement.

So  I’ve created my own by taking some reference.

Might be useful to someone like me:


public static string CreateCAMLQuery(List<string> parameters, string orAndCondition, bool isIncludeWhereClause)
{
 StringBuilder sb = new StringBuilder();

 if (parameters.Count == 0)
 {
AppendEQ(sb, "all");
 }

int j = 0;
for (int i = 0; i < parameters.Count; i++)
  {
   if (!string.IsNullOrEmpty(parameters[i].Split(';')[3]))
   {
    AppendEQ(sb, parameters[i]);

    if (i > 0 && j > 0)
    {
     sb.Insert(0, "<" + orAndCondition + ">");
     sb.Append("</" + orAndCondition + ">");
    }
    j++;
   }
  }
 if (isIncludeWhereClause)
 {
  sb.Insert(0, "<Where>");
  sb.Append("</Where>");
 }
 return sb.ToString();
}

public static void AppendEQ(StringBuilder sb, string value)
{
 string[] field = value.Split(';');

 sb.AppendFormat("<{0}>", field[2].ToString());
 sb.AppendFormat("<FieldRef Name='{0}'/>", field[0].ToString());
 sb.AppendFormat("<Value Type='{0}'>{1}</Value>", field[1].ToString(), field[3].ToString());
 sb.AppendFormat("</{0}>", field[2].ToString());
}

Now, you can prepare your query and call the function as per below to create dynamic query:


List<string> objColumns = new List<string>();

// here, format is like: "Column name(internal name of column);Column type; Conditional operator; Search string (input by user)

objColumns.Add("Title;Text;Contains;" + textbox1.Text.Trim());

objColumns.Add("FileLeafRef;Text;Contains;" + textbox1.Text.Trim());

SPQuery query = CreateCAMLQuery(objColumns, "Or", true);

Let me know your views or feedback.

Happy coding and SharePointing !

CQWP: Extract Usernames from AssignedTo (multi) column with separator

May 27, 2013 Leave a comment

Hi Guys,

Today, I need to do one quick POC.

SharePoint Task List, we are having assignedTo column which is taking multiple users.

In CQWP, it shows with some special characters with user id.

Getting Actual Result : User one;#16;#User two;#20;#User three

Expected Result should be: User one, User two, User three

So, with quick response, I had worked with some XSLT function which removes id (numeric values) from string and then replce special characters with separator.


<xsl:variable name="assignedToUsers">
 <xsl:call-template name="string-replace-all">
 <xsl:with-param name="text" select="translate(@AssignedTo, '0123456789', '')" />
 <xsl:with-param name="replace" select="';#;#'" />
 <xsl:with-param name="by" select="', '" />
 </xsl:call-template>
 </xsl:variable>
 <xsl:value-of select="$assignedToUsers" />

string-replace-all Template:

<xsl:template name="string-replace-all">
 <xsl:param name="text" />
 <xsl:param name="replace" />
 <xsl:param name="by" />
 <xsl:choose>
 <xsl:when test="contains($text, $replace)">
 <xsl:value-of select="substring-before($text,$replace)" />
 <xsl:value-of select="$by" />
 <xsl:call-template name="string-replace-all">
 <xsl:with-param name="text"
 select="substring-after($text,$replace)" />
 <xsl:with-param name="replace" select="$replace" />
 <xsl:with-param name="by" select="$by" />
 </xsl:call-template>
 </xsl:when>
 <xsl:otherwise>
 <xsl:value-of select="$text" />
 </xsl:otherwise>
 </xsl:choose>
 </xsl:template>

ref: http://geekswithblogs.net/Erik/archive/2008/04/01/120915.aspx

Thats it !

Happy SharePointing !!!

SharePoint 2010: Different XSL Functions to customize

March 6, 2013 Leave a comment

OOTB Web parts are very important in SharePoint.

And XSLT is useful to customize those web parts according to your need.

Here, I am going to list down function / templates which are useful in XSLT.

You can suggest me or ask if you need apart from this.

Remove HTML from value

<xsl:variable name="valDesc">
 <xsl:call-template name="removeHtmlTags">
 <xsl:with-param name="html" select="@Description" />
 </xsl:call-template>
 </xsl:variable>

<xsl:template name="removeHtmlTags">
 <xsl:param name="html"/>
 <xsl:choose>
 <xsl:when test="contains($html, '&lt;')">
 <xsl:value-of select="substring-before($html, '&lt;')"/>
 <!-- Recurse through HTML -->
 <xsl:call-template name="removeHtmlTags">
 <xsl:with-param name="html" select="substring-after($html, '&gt;')"/>
 </xsl:call-template>
 </xsl:when>
 <xsl:otherwise>
 <xsl:value-of select="$html"/>
 </xsl:otherwise>
 </xsl:choose>
 </xsl:template>

Convert value to Uppercase or lowercase for some condition

<xsl:variable name="lower">abcdefghijklmnopqrstuvwxyz</xsl:variable>
<xsl:variable name="upper">ABCDEFGHIJKLMNOPQRSTUVWXYZ</xsl:variable>

<xsl:variable name="myvalue" select="@Title" />

<xsl:if test="contains(translate(@TargetValue,$upper,$lower),translate($myvalue,$upper,$lower))">
 // your code
</xsl:if>

Click here for any string related functions.

Modified:

A good example of template collection for XSLT functions are available here

Happy SharePointing !!!

How To: Export Site Collection To XML

October 22, 2011 2 comments

Recently, I was working on project. And I need to know how many sites, lists available in our site collection.

I was looking out put in XML format.

So, I write utility which takes input of site collection and it export structure of site collection and lists.

public static void ExportToXML(string url)
        {
            XmlDocument doc = new XmlDocument();
            XmlNode docNode = doc.CreateXmlDeclaration("1.0", "UTF-8", null);
            doc.AppendChild(docNode);

            XmlNode sitesNode = doc.CreateElement("Site");
            XmlAttribute x_Url = doc.CreateAttribute("url");
            x_Url.Value = url;
            sitesNode.Attributes.Append(x_Url);

            XmlAttribute copygroupattr = doc.CreateAttribute("CopyGroups");
            copygroupattr.Value = "true";
            sitesNode.Attributes.Append(copygroupattr);

            SPSite site = new SPSite(url);
            // Console.WriteLine("<Site>");
            foreach (SPWeb web in site.AllWebs)
            {
                XmlNode webnode = doc.CreateElement("Web");
                XmlAttribute titleattribute = doc.CreateAttribute("title");
                titleattribute.Value = (web.Name.Length > 0) ? web.Name : web.Title;
                webnode.Attributes.Append(titleattribute);

                XmlAttribute weburlattribute = doc.CreateAttribute("url");
                weburlattribute.Value = web.Url;
                webnode.Attributes.Append(weburlattribute);

                Console.WriteLine("<web title=\"" + web.Name + "\" url=\"" + web.Url + "\">");
                foreach (SPList list in web.Lists)
                {

                    XmlNode listnode = doc.CreateElement("list");
                    XmlAttribute listattribute = doc.CreateAttribute("title");
                    listattribute.Value = list.Title;
                    listnode.Attributes.Append(listattribute);

                    XmlAttribute listurlattribute = doc.CreateAttribute("url");
                    listurlattribute.Value = list.DefaultViewUrl;
                    listnode.Attributes.Append(listurlattribute);
                    if (list.EventReceivers.Count > 0)
                    {
                        XmlAttribute listEventattribute = doc.CreateAttribute("EventHandler");
                        listEventattribute.Value = "true";
                        listnode.Attributes.Append(listEventattribute);
                    }
                    XmlAttribute listGuidattribute = doc.CreateAttribute("Guid");
                    listGuidattribute.Value = list.ID.ToString();
                    listnode.Attributes.Append(listGuidattribute);

                    Console.WriteLine("<list title=\"" + list.Title + "\" url=\"" + list.DefaultViewUrl + "\"  Guid=\"" + list.ID.ToString() + "\">");
                    //SPFieldCollection fields = list.Fields;
                    //foreach (SPListItem item in list.Items)
                    //{
                    //    Console.WriteLine("<item>");
                    //    foreach (SPField field in fields)
                    //    {
                    //        //note put a check for null field values here
                    //        Console.WriteLine("<property title=\"" + field.Title + "\" value=\"" + item[field.Id].ToString() + "\"/>");
                    //    }
                    //    Console.WriteLine("</item>");
                    //}
                    Console.WriteLine("</list>");
                    webnode.AppendChild(listnode);
                }
                Console.WriteLine("</web>");
                sitesNode.AppendChild(webnode);
            }
            Console.WriteLine("</site>");

            doc.AppendChild(sitesNode);
            doc.Save("C:\\Export.xml");
            Console.ReadLine();
        }

Ref: http://withinsharepoint.com/archives/87

Happy Coding!!!

How To: read xml file with xmldocument using XPath

April 8, 2011 Leave a comment

Xml File Location.xml:


<?xml version="1.0" encoding="utf-8"?>
<Location xmlns="urn:location-schema">
<Countries>
 <Country Name="India">
 <States>
 <State Code="GJ" Name="Gujarat">
 <Cities>
 <City Name="Ahmedabad" />
 <City Name="Surat" />
 <City Name="Baroda" />
 </Cities>
 </State>
 <State Code="MH" Name="Maharastra">
 <Cities>
 <City Name="Mumbai" />
 <City Name="Pune" />
 </Cities>
 </State>
 </States>
 </Country>
 <Country Name="USA">
 <States>
 <State Code="CA" Name="California">
 <Cities>
 <City Name="San Jose" />
 <City Name="Santa Cruz" />
 </Cities>
 </State>
 <State Code="NY" Name="New York">
 <Cities>
 <City Name="New York" />
 <City Name="Scranton" />
 </Cities>
 </State>
 </States>
 </Country>
</Countries>
</Location>

Now, Load XML File


XmlDocument xmldoc = new XmlDocument();
 xmldoc.Load(@"c:\Location.xml");
 XmlNamespaceManager LocationNM = new XmlNamespaceManager(xmldoc.NameTable);
 LocationNM.AddNamespace("lo", "urn:location-schema");

How to get all countries from xml

XmlNodeList CountryList = xmldoc.SelectNodes("/lo:Location/lo:Countries/lo:Country", LocationNM);
if (CountryList != null && CountryList.Count > 0)
 {
 foreach (XmlNode CountryNode in CountryList)
 {
 Response.Write("Country - " + CountryNode.Attributes["Name"].Value + "<br>");
 }
 }

How to get all states from xml

XmlNodeList StateList = xmldoc.SelectNodes("/lo:Location/lo:Countries/lo:Country/lo:States/lo:State", LocationNM);
 if (StateList != null && StateList.Count > 0)
 {
 foreach (XmlNode StateNode in StateList)
 {
 Response.Write("State - " + StateNode.Attributes["Name"].Value + "<br>");
 }
 }
Categories: asp.net, C#, Uncategorized, XML