Generate a hierarchical XML file from SharePoint list

Situation
You need to get data from a SharePoint 2010 list and generate a hierarchical XML file that can be used as a data source for a Flash movie on a site. Your enterprise SharePoint Admins want their platform to stay as out of the box as possible, and they have good reasons to minimize server-side customizations and the use of SharePoint Designer.

Opportunity
Undaunted, you turn to the SharePoint Client Object Model for a console application that can be launched from a button on a page or by a workflow. In effect your app needs to perform the following automation tasks:

  • Get a flat XML file of the data
  • Convert the flat XML file into a hierarchical file
  • Save the file as an XML document and upload it to a SharePoint document library

Step 1 – Get data to a flat XML file
The first step is to get data from a SharePoint list and store it as XML in memory. This could be done in one of several ways: SPList class and a CAML SPList class and LINQ query, Web Services, GetListItems, or the URL protocol of the RPC method. This solution will use URL Protocol, because it’s a simple and transparent way to access the data. This step also needs to:

  • Get the XML output from a special View of the list rather than the default view of the list, so all of the columns can be shown all of the time without affecting data entry or management
  • Trim off any extraneous characters from the values that come from SharePoint lists, like dates, URLs, or strings delimited with “;#”
  • Resolve a potential SP2010 security exception when accessing from the localhost
  • Build a valid URL to the document, regardless of whether it is a PDF or a Web link
using System;
using System.Linq;
using System.Xml;
using System.Xml.Linq;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
// used for uploading - clientcontext and memorystream
using Microsoft.SharePoint.Client;
using System.IO;

namespace PMGDialXML
{
    class Program
    {
        static void Main(string[] args)
        {
            // Use the URL protocol of the RPC method to get data from
            // the "XML Generator View" which displays all of the fields
            string sUrl = "http://rootweb/site/subsite/_vti_bin/owssvr.dll?Cmd=Display&List={E3DFD89F-4DA3-4418-8674-D692B59CCBC4}&View={4E44F267-C5F7-42B6-8871-0194604177EA}&XMLDATA=TRUE";

            // Resolve a potential SP2010 security exception when accessing from the localhost
	         XmlUrlResolver xmlResolver = new XmlUrlResolver();
	         xmlResolver.Credentials = System.Net.CredentialCache.DefaultCredentials;
	         XmlReaderSettings xmlReaderSettings = new XmlReaderSettings();
	         xmlReaderSettings.XmlResolver = xmlResolver;

            // Define LINQ Namespace objects (System.XML.Linq)
            XNamespace s = "uuid:BDC6E3FA-6DA3-11d1-A2A3-00AA00C14882";
            XNamespace dt = "uuid:C2F41030-65B3-11d1-A29F-00AA00C14882";
            XNamespace rs = "urn:schemas-microsoft-com:rowset";
            XNamespace z = "#RowsetSchema";

            // Get the list XML (System.XML)
            XDocument sUrlDoc = XDocument.Load(XmlReader.Create(sUrl, xmlReaderSettings));

            // Store fields returned from owssrv in a new XML variable
            var xVar =
                new XElement("vWorkflows",
                    sUrlDoc.Root.Descendants(z + "row").Select(r => new XElement("vSection",
                        new XAttribute("sectionTitle", TrimValue(r.Attribute("ows_SectionTitle").Value)),
                        new XAttribute("sectionId", TrimValue(r.Attribute("ows_SectionID").Value)),
                        new XAttribute("show", (r.Attribute("ows_ShowOrHide").Value)),
                        new XElement("vCategory",
                            new XAttribute("catTitle", TrimValue(r.Attribute("ows_CategoryTitle").Value)),
                            new XAttribute("catRef", TrimValue(r.Attribute("ows_CategoryID").Value)),
                            new XElement("vType",
                                new XAttribute("typeTitle", TrimValue(r.Attribute("ows_TypeTitle").Value)),
                                new XAttribute("typeRef", TrimValue(r.Attribute("ows_TypeID").Value)),
                                new XElement("vLink",
                                    new XAttribute("linkNum", ""),
                                    new XElement("linkTitle", TrimValue(r.Attribute("ows_Document").Value)),
                                    new XElement("linkUrl", WhatsUpDoc(TrimValue(r.Attribute("ows_Document_x003a_FileName").Value) + ";" + TrimValue(r.Attribute("ows_Dial_x0020_Title_x003a_DocumentU").Value))),
                                    new XElement("linkSummary", ""),
                                    new XElement("linkKeywords", ""),
                                    new XElement("pubDate", TrimDate(TrimValue(r.Attribute("ows_Document_x003a_PublishDate").Value))),
                                    new XElement("lastUpdate", TrimDate(TrimValue(r.Attribute("ows_Document_x003a_LastUpdated").Value)))
                                )
                            )
                        )
                    )
                )
            );

Step 2 – Construct hierarchical XML document
Read (aka query) the in-memory XML and reconstruct the a collection of elements and attributes into a hierarchical XML output using LINQ to XML. Note that the results of one LINQ query expression can be the input of another LINQ expression. Code it in a way that matched up with the XML output to make it easier for those that follow. And a few other things:

  • If item is set to “Show” in the list then show it, otherwise hide it.
  • Sort the output by SectionId rather than by SectionTitle to conform to the SWF design and match the existing XML exactly
           // Create new hierarchical XML document from the flat XML variable
            XDocument xDoc = new XDocument(
                new XDeclaration("1.0", "utf-8", "yes"),
                new XComment("XML Source Data for Dial Flash"),
                new XElement("workflows",
                    from sec in xVar.Elements("vSection")
                    where (string)sec.Attribute("show").Value == "Show"
                    orderby (string)sec.Attribute("sectionId").Value ascending
                    group sec by new {
                        secT = (string)sec.Attribute("sectionTitle").Value,
                        secId = (string)sec.Attribute("sectionId").Value
                    } into gsec
                    select new XElement("section",
                        new XAttribute("sectionTitle", gsec.Key.secT),
                        new XAttribute("sectionId", gsec.Key.secId),
                        from cat in gsec.Elements("vCategory")
                        orderby (string)cat.Attribute("catTitle").Value ascending
                        group cat by new {
                            catT = (string)cat.Attribute("catTitle").Value,
                            catId = (string)cat.Attribute("catRef").Value
                        } into gcat
                        select new XElement("category",
                            new XAttribute("catTitle", gcat.Key.catT),
                            new XAttribute("catRef", gcat.Key.catId),
                            from typ in gcat.Elements("vType")
                            orderby (string)typ.Attribute("typeTitle").Value ascending
                            group typ by new {
                                typT = (string)typ.Attribute("typeTitle").Value,
                                typId = (string)typ.Attribute("typeRef").Value,
                            } into gtyp
                            select new XElement("type",
                                new XAttribute("typeTitle", gtyp.Key.typT),
                                new XAttribute("typeRef", gtyp.Key.typId),
                                from lnk in gtyp.Elements("vLink")
                                orderby (string)lnk.Attribute("linkNum").Value ascending
                                group lnk by new {
                                    linN = (string)lnk.Attribute("linkNum").Value,
                                    linT = (string)lnk.Element("linkTitle").Value,
                                    linU = (string)lnk.Element("linkUrl").Value,
                                    linS = (string)lnk.Element("linkSummary").Value,
                                    linK = (string)lnk.Element("linkKeywords").Value,
                                    pubD = (string)lnk.Element("pubDate").Value,
                                    lasU = (string)lnk.Element("lastUpdate").Value
                                } into glnk
                                select new XElement("link",
                                    new XAttribute("linkNum", glnk.Key.linN),
                                    new XElement("linkTitle", glnk.Key.linT),
                                    new XElement("linkUrl", glnk.Key.linU),
                                    new XElement("linkSummary", glnk.Key.linS),
                                    new XElement("linkKeywords", glnk.Key.linK),
                                    new XElement("pubDate", glnk.Key.pubD),
                                    new XElement("lastUpdate", glnk.Key.lasU)
                                )
                            )
                        )
                    )
                )
            );

Yep, there’s another way to do it that groups the top level, but we won’t do that here. It looks something like this …

	   // Alternate way to do it
	   var query = xVar.Elements("vSection").
	      OrderBy(grp => (string)grp.Attribute("sectionTitle").Value).
	      GroupBy(grp => (string)grp.Attribute("sectionTitle")).
	      Select(grp => new XElement("section", grp.First().Attributes(),
	            grp.Select(vsec => new XElement("category",
		       vsec.Element("vCategory").Attributes(),
		       vsec.Element("vCategory").Elements()
		       )
		    )
	         )
	   );

	   var xml = new XElement("workflows", query);

Step 3. Save XML document to document library
This is where the Client Object Model comes in. You’ll also use System.IO for memorystream.

            // Save XML Document to a string
            string upDoc = xDoc.ToString();

            // Upload XML Document to a SharePoint Document Library
            UploadXmlFile(upDoc);
        
            public static void UploadXmlFile(string xmlContent)
            {
            // Define the site, library and file variables
            string webUrl = "http://whatevertheroot.dot",
                   siteUrl = "/site/subsite/subsubsite",
                   libraryName = "documents",
                   fileName = "swf_file.xml";

            // Instantiate the site
            ClientContext clientContext = new ClientContext(webUrl + siteUrl);

            // Process the XML file
            using (MemoryStream memoryStream = new MemoryStream())
            {
                // Write the file XML contents into a MemoryStream object ...
                StreamWriter writer = new StreamWriter(memoryStream);
                writer.Write(xmlContent);
                writer.Flush();
                memoryStream.Position = 0;

                // ... and save it in the Document Library (set to "true" to overwrite the file)
                Microsoft.SharePoint.Client.File.SaveBinaryDirect(clientContext, siteUrl + "/" + libraryName + "/" + fileName, memoryStream, true);
            }

Results
This code feeds a Flash movie that allows users to dial in on documents by a predefined taxonomy on a SharePoint site. What once required hours of manual XML file edits and uploads can now be done in one click, and the door is now open to automating the entire document upload, approval and management process using workflows and InfoPath web forms. Presumably, this kind of approach can also be used with other kinds of animated web parts that use XML as a data source.

Get the BDC Identity value from a SharePoint 2010 External List to SQL Server

Why bother? Well, because without the BDC Identity value, it is nearly impossible to hyperlink directly to the Edit form of an External List from anywhere on a SharePoint web site.

Say you have a Reporting Services web part, and you want to position users just one click away from of changing a value from red to green in the report. If you could access the BDC Identity value from SQL and SSRS, you could add an Go to URL Action to an element that opens the External List to a specific Edit form with a URL string that contains the BDC Identity in its &Source value.

This task needs to be done by people in the department that create the data – not by techs or Server Administrators. This is SharePoint, so they expect something simple or out-of-the-box to do the job.

Simply using a URL query string with FilterFields on some other value in the external list will open the external list and filter on the value, but the UX requires two clicks and leaves two windows to close after each procedure. And if there is a way to copy the BDC Identity to SQL using jQuery, ECMAScript, or a Workflow, it did not reveal itself.

As it turns out, a low cost and low demand way to accomplish this task can be to build a Visual Studio 2010 C# Express Console Application that follows the Client Object Model. It can be deployed to a shared drive on a server as an EXE file that can then be run by subject matter experts from a desktop shortcut.

The following Program.cs, for example, uses the Microsoft.SharePoint.Client and Microsoft.SharePoint.Client.Runtime dll’s to open the SharePoint site and find the External List by name.

using System;
using Microsoft.SharePoint.Client;

class Program
{
   public static void Main()
    {

    String siteUrl = "http://whatever/whatever";

	    ClientContext clientContext = new ClientContext(siteUrl);

	    List list = clientContext.Web.Lists
	        .GetByTitle("name of external list");
	    CamlQuery camlQuery = new CamlQuery();

	    // RowLimit will have no effect
	    camlQuery.ViewXml =

It uses this XML section to return all empty fields in a column of SQL origin with a string datatype called “CopyOfBdcIdentity.”

	        @"<View>
	            <Query>
	              <Where>
	                <IsNull>
	                    <FieldRef Name='CopyOfBdcIdentity' />
	                </IsNull>
	              </Where>
	              <OrderBy>
                      <FieldRef Name='Group'/>
	              </OrderBy>
	            </Query>
	            <ViewFields>
                        <FieldRef Name='ID'/>
	                <FieldRef Name='Title'/>
	                <FieldRef Name='BdcIdentity'/>
	                <FieldRef Name='CopyOfBdcIdentity'/>
	            </ViewFields>
	            <RowLimit>1</RowLimit>
	        </View>";

It then loops through the results, copies the BDC Identity value as a string to the CopyOfBdcIdentity in each item, and reports on the results.

	    // include referenced field here as well
	    ListItemCollection listItems = list.GetItems(camlQuery);
	    clientContext.Load(
	         listItems,
	         items => items
	             .Include(
	                item => item["ID"],
	                item => item["Title"],
	                item => item["BdcIdentity"],
	                item => item["CopyOfBdcIdentity"]));
	    clientContext.ExecuteQuery();

	    // loop through the result
	    foreach (ListItem listItem in listItems)
	    {
            listItem["CopyOfBdcIdentity"] = listItem["BdcIdentity"].ToString();

            // update all fields
            listItem.Update();

            // display the result
            Console.WriteLine("SpreadKey: '{0}', Group: '{1}'",
	            listItem.FieldValues["ID"],
	            listItem.FieldValues["Title"]);
	    }

	    // invoke ExecuteQuery a second time
            clientContext.ExecuteQuery();

	    Console.Write("Update complete, press Enter to continue!");
	    Console.ReadLine();
   }
}