Generate a hierarchical XML file from SharePoint list
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 without SharePoint Designer by building a console application that can be launched from a button on a page or from 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
usingSystem;usingSystem.Linq;usingSystem.Xml;usingSystem.Xml.Linq;usingSystem.Collections.Generic;usingSystem.Text;usingSystem.Windows.Forms;// used for uploading - clientcontext and memorystreamusingMicrosoft.SharePoint.Client;usingSystem.IO;namespacePMGDialXML{classProgram{staticvoidMain(string[] args){// Use the URL protocol of the RPC method to get data from// the "XML Generator View" which displays all of the fieldsstring 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 localhostXmlUrlResolver xmlResolver =newXmlUrlResolver();
xmlResolver.Credentials = System.Net.CredentialCache.DefaultCredentials;XmlReaderSettings xmlReaderSettings =newXmlReaderSettings();
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 variablevar xVar =newXElement("vWorkflows", sUrlDoc.Root.Descendants(z +"row").Select(r =>newXElement("vSection",newXAttribute("sectionTitle",TrimValue(r.Attribute("ows_SectionTitle").Value)),newXAttribute("sectionId",TrimValue(r.Attribute("ows_SectionID").Value)),newXAttribute("show",(r.Attribute("ows_ShowOrHide").Value)),newXElement("vCategory",newXAttribute("catTitle",TrimValue(r.Attribute("ows_CategoryTitle").Value)),newXAttribute("catRef",TrimValue(r.Attribute("ows_CategoryID").Value)),newXElement("vType",newXAttribute("typeTitle",TrimValue(r.Attribute("ows_TypeTitle").Value)),newXAttribute("typeRef",TrimValue(r.Attribute("ows_TypeID").Value)),newXElement("vLink",newXAttribute("linkNum",""),newXElement("linkTitle",TrimValue(r.Attribute("ows_Document").Value)),newXElement("linkUrl",WhatsUpDoc(TrimValue(r.Attribute("ows_Document_x003a_FileName").Value)+";"+TrimValue(r.Attribute("ows_Dial_x0020_Title_x003a_DocumentU").Value))),newXElement("linkSummary",""),newXElement("linkKeywords",""),newXElement("pubDate",TrimDate(TrimValue(r.Attribute("ows_Document_x003a_PublishDate").Value))),newXElement("lastUpdate",TrimDate(TrimValue(r.Attribute("ows_Document_x003a_LastUpdated").Value)))))))));
C#
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 variableXDocument xDoc =newXDocument(newXDeclaration("1.0","utf-8","yes"),newXComment("XML Source Data for Dial Flash"),newXElement("workflows",from sec in xVar.Elements("vSection")where(string)sec.Attribute("show").Value =="Show"orderby(string)sec.Attribute("sectionId").Value ascendinggroup sec bynew{
secT =(string)sec.Attribute("sectionTitle").Value,
secId =(string)sec.Attribute("sectionId").Value
}into gsec
selectnewXElement("section",newXAttribute("sectionTitle", gsec.Key.secT),newXAttribute("sectionId", gsec.Key.secId),from cat in gsec.Elements("vCategory")orderby(string)cat.Attribute("catTitle").Value ascendinggroup cat bynew{
catT =(string)cat.Attribute("catTitle").Value,
catId =(string)cat.Attribute("catRef").Value
}into gcat
selectnewXElement("category",newXAttribute("catTitle", gcat.Key.catT),newXAttribute("catRef", gcat.Key.catId),from typ in gcat.Elements("vType")orderby(string)typ.Attribute("typeTitle").Value ascendinggroup typ bynew{
typT =(string)typ.Attribute("typeTitle").Value,
typId =(string)typ.Attribute("typeRef").Value,}into gtyp
selectnewXElement("type",newXAttribute("typeTitle", gtyp.Key.typT),newXAttribute("typeRef", gtyp.Key.typId),from lnk in gtyp.Elements("vLink")orderby(string)lnk.Attribute("linkNum").Value ascendinggroup lnk bynew{
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
selectnewXElement("link",newXAttribute("linkNum", glnk.Key.linN),newXElement("linkTitle", glnk.Key.linT),newXElement("linkUrl", glnk.Key.linU),newXElement("linkSummary", glnk.Key.linS),newXElement("linkKeywords", glnk.Key.linK),newXElement("pubDate", glnk.Key.pubD),newXElement("lastUpdate", glnk.Key.lasU)))))));
C#
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 itvar query = xVar.Elements("vSection").OrderBy(grp =>(string)grp.Attribute("sectionTitle").Value).GroupBy(grp =>(string)grp.Attribute("sectionTitle")).Select(grp =>newXElement("section", grp.First().Attributes(),
grp.Select(vsec =>newXElement("category",
vsec.Element("vCategory").Attributes(),
vsec.Element("vCategory").Elements()))));var xml =newXElement("workflows", query);
C#
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 stringstring upDoc = xDoc.ToString();// Upload XML Document to a SharePoint Document LibraryUploadXmlFile(upDoc);publicstaticvoidUploadXmlFile(string xmlContent){// Define the site, library and file variablesstring webUrl ="http://whatevertheroot.dot",
siteUrl ="/site/subsite/subsubsite",
libraryName ="documents",
fileName ="swf_file.xml";// Instantiate the siteClientContext clientContext =newClientContext(webUrl + siteUrl);// Process the XML fileusing(MemoryStream memoryStream =newMemoryStream()){// Write the file XML contents into a MemoryStream object ...StreamWriter writer =newStreamWriter(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);}}
C#
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.