Sharepoint 2007 Batch Update example using web services and CAML

Here is an example of running a batch update process on Sharepoint 2007 using web services. It was written in C# in Visual Studio 2008.

In this example, I take the value of a text field "Date_x0020_of_x0020_Birth", turn this into a DateTime value, and then use this to update the the value of a DateTime field "Date_x0020_of_x0020_Birth0". Obviously this section of the code will not be particularly generalisable, however I have left in all the code in order to show you the principles involved, and hopefully you will find it useful in building your own batch routines.

In order to get it running, you must create a reference to the Sharepoint Lists web service and call it "ListsService". The SDK shows you how to do this, and is available here: http://www.microsoft.com/downloads/en/details.aspx?FamilyId=6D94E307-67D9-41AC-B2D6-0074D6286FA9&displaylang=en

The GetListItems() method of the Lists web service is used to query the data, and the UpdateListItems() method is used to update items.

I used an XPathNavigator object to filter only rows that contain data. You could just loop through the nodes if you prefer.

CAML is used to query the Sharepoint list and look for items where the "Date_x0020_of_x0020_Birth" is null. Tailor this to your own needs.
For CAML reference, look at the MSDN article here: http://msdn.microsoft.com/en-us/library/lists.lists.getlistitems%28v=office.12%29.aspx

The CAML update command uses owshiddenversion, and so is useful in situations where versioning is enabled. Lists without versioning do not need this field.

If the UpdateListItems command returns an error, in my experience this is usually caused by using an incorrect internal name for a field.  You may need to add the URI prefix "urn:schemas-microsoft-com:office:office#" to the field name (e.g. change line 42 to something like:  "<Field Name=\"urn:schemas-microsoft-com:office:office#{4}\">{5}</Field>").

Full code:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Utilities;
using System.IO;
using System.Xml;
using System.Xml.XPath;
using System.Net;

namespace BatchUpdateDOB
{
    class Program
    {
        static string LogFile = "D:\\BatchUpdateLog.txt";  // Log file location

        static void Main(string[] args)
        {
            try
            {
                StatusMessage("Writing log file to: " + LogFile);
           
                // Set up the variables to be used.
                StringBuilder methodBuilder = new StringBuilder();
                string batch = string.Empty;
                DateTime currentDate = DateTime.Now;
                string formattedDate = SPUtility.CreateISO8601DateTimeFromSystemDateTime(currentDate);
                string fieldToReference = "Date_x0020_of_x0020_Birth";
                string fieldToUpdate = "Date_x0020_of_x0020_Birth0";

                string batchFormat = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
                  "<ows:Batch OnError=\"Return\">{0}</ows:Batch>";

                
                // CAML update command for web service
                string methodFormat = "<Method ID=\"1\" Cmd=\"Update\">" +
                 "<Field Name=\"ID\">{1}</Field>" +
                 "<Field Name=\"FileRef\">{2}</Field>" +
                 "<Field Name=\"owshiddenversion\">{3}</Field>" +
                 "<Field Name=\"{4}\">{5}</Field>" +
                 "</Method>";

                //UPDATE THESE TWO LINES TO SITE SPECIFIC VALUES
                string siteName = "http://<your site root uri here>";
                string listName = "<your list name here>";

                //string viewName = "";  // No need for viewname
                int rowLimit = 10;       //The number of items returned per batch (not used)

                StatusMessage("Processing " + siteName + ", List: " + listName + "...");

                //Web service setup
                //ListService should be the name of your web service reference
                ListService.Lists listService = new ListService.Lists();
                listService.Credentials = System.Net.CredentialCache.DefaultCredentials;
                listService.Url = siteName + "/_vti_bin/Lists.asmx";
                

                //Build CAML query to search for records
                XmlDocument camlDocument = new XmlDocument();
                XmlNode queryNode = camlDocument.CreateElement("Query");
                //queryNode.InnerXml =  "<Where><IsNull><FieldRef Name='Date_x0020_of_x0020_Birth'/></IsNull></Where>";
                //queryNode.InnerXml = "<OrderBy><FieldRef Name='Country' /></OrderBy>";

                XmlNode viewFieldsNode = camlDocument.CreateElement("ViewFields");
                viewFieldsNode.InnerXml = "<FieldRef Name='Title' />" +
                  "<FieldRef Name='owshiddenversion' />" +
                  "<FieldRef Name='" + fieldToReference + "' />" +
                  "<FieldRef Name='" + fieldToUpdate + "' />" +
                  "<FieldRef Name='FileRef' />";

                XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");
                queryOptionsNode.InnerXml = "<ViewAttributes Scope=\"Recursive\" /><IncludeMandatoryColumns>True</IncludeMandatoryColumns>";

                // Get the GUID of the list
                XmlNode ndListView = listService.GetListAndView(listName, "");
                string listGuid = ndListView.ChildNodes[0].Attributes["Name"].Value;
                string listVersion = ndListView.ChildNodes[0].Attributes["Version"].Value;

                // Run the CAML query
                XmlNode resultNode = listService.GetListItems(listGuid, null, queryNode, viewFieldsNode, rowLimit.ToString(), queryOptionsNode, null);
                // If you want to put a rowlimit on, then use this command:
                //XmlNode resultNode = listService.GetListItems(listGUID, null, queryNode, viewFieldsNode, rowLimit.ToString(), queryOptionsNode, null);

                long totalRecords = 0;
               
                //string itemDOB;
                string dob;
                DateTime dtDOB;
                string owsHiddenVersion;  
                string fullFileRef;
                string owsFileRef;
               

                // Create xpath navigator   
                XPathNavigator navigator = resultNode.CreateNavigator();
                // Set up namespace manager for XPath   
                XmlNamespaceManager ns = new XmlNamespaceManager(navigator.NameTable);
                ns.AddNamespace("rs", "urn:schemas-microsoft-com:rowset");
                ns.AddNamespace("z", "#RowSetSchema");

                // Select only the z:row nodes
                XmlNodeList nodes = resultNode.SelectNodes("//*[name()='z:row'] ", ns);

                // Iterate through the returned nodes
                foreach (XmlNode node in nodes)
                {
                    string itemID = node.Attributes["ows_ID"].InnerText;
                    try
                    {
                        dob = node.Attributes["ows_" + fieldToReference].InnerText;
                        owsHiddenVersion = node.Attributes["ows_" + "owshiddenversion"].InnerText;
                        owsFileRef = node.Attributes["ows_" + "FileRef"].InnerText;
                        // For some reason my data is returned in the format: 2#/listname/path, 
                        // where 2 seems to be an id or version number, and listname/path points to the file in relation to the root site.
                        // The following strips that out and gets the full path (required for the CAML update)
                        fullFileRef = siteName + "/" + owsFileRef.Remove(0, owsFileRef.IndexOf("#") + 1);
                    }
                    catch(Exception ex)
                    {
                        // Ignore this row
                        //StatusMessage("ERROR: Cannot process: " + node.InnerXml.ToString() + ": " + ex.Message); // If you want to see why, uncomment
                        continue;
                    }

                    if (String.IsNullOrEmpty(dob)) continue;  // Ignore list items with no value

                    dtDOB = Convert.ToDateTime("01/01/1900"); // Set default value for dates before 1900

                    // Convert date text to DateTime
                    try
                    {
                        dtDOB = Convert.ToDateTime(dob);
                    }
                    catch (Exception ex)
                    {
                        StatusMessage("Cannot process: " + siteName + "/" + fullFileRef + " DOB: " + dob + ".  Setting to 01/01/1900. " + ex.Message);
                    }

                    MessageToFile("Processing: " + fullFileRef + ", DOB = " + dob);

                    methodBuilder.AppendFormat(methodFormat, listGuid, itemID, fullFileRef, owsHiddenVersion, fieldToUpdate, SPUtility.CreateISO8601DateTimeFromSystemDateTime(dtDOB));

                    // Put the pieces together.
                    batch = string.Format(batchFormat, methodBuilder.ToString());
                    //MessageToFile(batch);

                    //Build CAML command to update using Web service
                    XmlDocument xmlDoc = new XmlDocument();
                    XmlElement elBatch = xmlDoc.CreateElement("Batch");
                    elBatch.SetAttribute("OnError", "Continue");
                    elBatch.SetAttribute("ListVersion", listVersion.ToString());
                    elBatch.SetAttribute("ViewName", "");
                    elBatch.InnerXml = methodBuilder.ToString();

                    // This line runs the CAML update command
                    XmlNode updatesResponse = listService.UpdateListItems(listGuid, elBatch);
                    if (updatesResponse.FirstChild.FirstChild.InnerText != "0x00000000") throw new Exception("Could not update properties.\n\n" + updatesResponse.InnerText + "\n" + updatesResponse.FirstChild.FirstChild.InnerText);
                    StatusMessage(fullFileRef + " processed...");

                    totalRecords++;
                }
                StatusMessage("Completed - " + totalRecords + " processed.");
            }
            catch (Exception exp)
            {
                StatusMessage("ERROR: " + exp.Message);
                
            }
            
        }

        
        /// <summary>
        /// Display a  message
        /// </summary>
        /// <param name="myMessage"></param>
        static void StatusMessage(string myMessage)
        {
            Console.WriteLine(myMessage.ToString()); 
            MessageToFile(myMessage);
        }

        /// <summary>
        /// Write message to the log file
        /// </summary>
        /// <param name="myMessage"></param>
        static void MessageToFile(string myMessage)
        {
            using (StreamWriter sw = new StreamWriter(LogFile, true))
            {
                DateTime now = DateTime.Now;
                sw.WriteLine(now.ToString() + ": " + myMessage.ToString());
            }
        }
    }
}

Set Sharepoint meta-data from VBA using updatelist web service

For the top ten best selling Rolex watches, visit Yngoo!
I've been working on a problem trying to integrate a legacy Microsoft Office 2003 VBA application with Sharepoint 2007. In theory, I should be able to use the SOAP toolkit provided my Microsoft to talk to the Sharepoint (WSS) web services, but in practice there was very little documentation of how to do this, and I could find no resources on the internet aside from the MSDN definitions of the web services, and only a little attached to the SOAP toolkit (available here: Microsoft Office 2003 Web Services Toolkit ).

The basic problem I was trying to solve was: how do I set meta-data held on a document in Sharepoint from Office 2003 using VBA?

After much head scratching I finally solved this by making some assumptions based on the MSDN reference documentation, then using the Fiddler tool until I had worked out what I was doing wrong.  Fiddler allows you to inspect the HTTP calls and responses that you are issuing/receiving.  This is very helpful when debugging web services, and well worth looking into if you are doing anything in this area.

Anyway, through Fiddler I could see the response from the web service, including the error message.  This meant that my initial best guess on how to structure the XML was at least nearly correct, because the web service was giving me a proper error message.  Eventually I had a properly structured call and everything worked!


Remember to install the SOAP toolkit before you try this.  You will then be able to add this to your VBA project and add a web reference to your project to the WSS Lists.asmx service as you would if your using .Net.  This is all explained in the MSDN link above but drop a comment if you would like me to go into more detail.

The actual VBA code used structure the call to the web service looks like this:

'Define a new list service web service class
Dim listws As New clsws_Lists

' Set up the batch command used to set the meta data. 
' Note that FileRef is set to URL of the file you want to change
'ListVersion is from my test site, you may not need that
' First set up DOM document containing fields
Dim xmlDoc As New MSXML2.DOMDocument30
xmlDoc.async = False
Dim xmlText As String
xmlText = "<root>" + _
  "<Batch OnError='Continue' ListVersion='59' PreCalc='TRUE' xmlns=''>" + _
      "<Method ID='1' Cmd='Update'>" + _
        "<Field Name='ID' />" + _
        "<Field Name='FileRef'>http://myservername:1080/sites/mysite/shared documents/TestWebService.doc</Field>" + _
        "<Field Name='Title'>Uploaded from VBA</Field>" + _
        "<Field Name='Surname'>" + sLastName + "</Field>" + _
        "<Field Name='Forename'>" + sFirstName + "</Field>" + _
        "<Field Name='Date_x0020_of_x0020_Birth'>" + sDOB + "</Field>" + _
      "</Method>" + _
    "</Batch>" + _
  "</root>"
xmlDoc.LoadXml (xmlText)

' This is a bit of debug that checks the XML is well formed and show you it if it is. 
If xmlDoc.parseError.ErrorCode <> 0 Then
    Dim myErr
    Set myErr = xmlDoc.parseError
    MsgBox (myErr.reason)
Else
    MsgBox xmlDoc.XML
End If

' Set up IXMLDOMNodeList object
Dim myXMLNodeList As MSXML2.IXMLDOMNodeList
Dim root As MSXML2.IXMLDOMElement
' Now the the XML node list to the batch command we have just constructed above
Set root = xmlDoc.documentElement
Set myXMLNodeList = root.ChildNodes

' Now run the web service to update the meta-data.
' Note that the first parameter is the GUID of the list that holds the data you want to change.  I can't get this to take a list name - it only seems to wrok with a GUID.
Dim updateReturn As IXMLDOMNodeList
Set updateReturn = listws.wsm_UpdateListItems("{B768B024-8B98-4918-990A-ECE34691DBBC}", myXMLNodeList)

If you need to find out the GUID or ListVersion values for your list, use the "GetListCollection" web service and iterate through the XML it returns:

Dim listCollection As IXMLDOMNode
Set listCollection = listws.wsm_GetListCollection
' You can iterate this to find the GUID of our list

I can post further examples if anybody wants any.

*Edit* - I have written another blog post on how to find the GUID of lists in VBA here: http://the-simple-programmer.blogspot.com/2010/04/vba-code-to-iterate-through-results-of.html
This simple function will return the version number and the GUID of the Sharepoint list from its name.  

You may also be interested in teh following post: :  VBA code to check in a document to Sharepoint and set meta data.

Creating Sharepoint list fields on the fly with web services

I've recently been trying to create Sharepoint 2007 list fields on the fly using the Lists.UpdateLists() method described here:
http://msdn.microsoft.com/en-us/library/lists.lists.updatelist.aspx

Unfortunately, after modifying the sample code to my needs, a SOAP exception kept getting thrown: "NodeType 'Element' is not an valid type":

<errorstring xmlns="http://schemas.microsoft.com/sharepoint/soap/">'Element' is an invalid XmlNodeType. Line 1, position 10.</errorstring>

Using Visual Studio 2005 to debug,  I have been unable to work out what the problem was.  The CAML query built in the webservice to create the field must be incorrect, but the innerXML of the CAML query (I was only using the "updateFields" node) looked fine.  Clearly the problem was elsewhere in the call, but is not clear to me how the call is constructed once the parameters are passed to the web service.

SOAP does not seem to me to give a huge amount of debugging information when a call fails.  When I create my own web services, I can use VS to step through them and get lots of debug information, but I haven't been able to work out how to do this with the MS DLLs yet.

Anyway, I eventually solved this problem by going back to the example and creating another copy of the example Microsoft code with minimal changes in order to point the call at my webservice.  This proved to work OK, although it did create two sample fields (Field1 and Field2) that I cannot remove using the Sharepoint IE front end!.  I suspect this is becuase they are created as required fields.

By making minimal iterative changes, I finally managed to create the following c# method which successfully creates the fields on the fly, and even gives them an appropriate Sharepoint type.

(Please note this code looks better in IE than Firefox which is not wrapping the lines - click on "View Plain" to view or copy in Firefox)

public bool CreateNewField(ListInfo listInfo, string fieldName, object fieldValue)
{
    bool canCreateField = true;
    try
    {

                //ListsService.Lists listService = new ListsService.Lists();
                //listService.Credentials= System.Net.CredentialCache.DefaultCredentials;
                //NOTE: I have already instatiated my connection (listService) and the GUID of the list (listInfo.listName)

                XmlNode ndList = listService.GetList(listInfo.listName);
                XmlNode ndVersion = ndList.Attributes["Version"];

                XmlDocument xmlDoc = new System.Xml.XmlDocument();

               

                XmlNode ndNewFields = xmlDoc.CreateNode(XmlNodeType.Element,
                    "Fields", "");
                XmlNode ndUpdateFields = xmlDoc.CreateNode(XmlNodeType.Element,
                    "Fields", "");

               
                
                // Field types
                if(fieldValue.GetType() == Type.GetType("System.String"))
                {
                    ndNewFields.InnerXml = @"<Method ID='1'>" +
                   "<Field Type='Text' DisplayName='" + fieldName + "' Required='FALSE' FromBaseType='FALSE' Description='Generated Field'/>" +
                   "</Method>";
                }
                else if (fieldValue.GetType() == Type.GetType("System.Int") || 
                         fieldValue.GetType() == Type.GetType("System.UInt16") ||
                    fieldValue.GetType() == Type.GetType("System.UInt32") ||
                    fieldValue.GetType() == Type.GetType("System.UInt64")
                    )
                {
                    ndNewFields.InnerXml = @"<Method ID='1'>" +
                  "<Field Type='Integer' DisplayName='" + fieldName + "' Required='FALSE' FromBaseType='FALSE' Description='Generated Field'/>" +
                  "</Method>";
                }
                else if (fieldValue.GetType() == Type.GetType("System.Double"))
                {
                    ndNewFields.InnerXml = @"<Method ID='1'>" +
                  "<Field Type='Number' DisplayName='" + fieldName + "' Required='FALSE' FromBaseType='FALSE' Description='Generated Field'/>" +
                  "</Method>";
                }
                else if (fieldValue.GetType() == Type.GetType("System.Date"))
                {
                    ndNewFields.InnerXml = @"<Method ID='1'>" +
                  "<Field Type='DateTime' DisplayName='" + fieldName + "' Required='FALSE' FromBaseType='FALSE' Description='Generated Field'/>" +
                  "</Method>";
                }
                else if (fieldValue.GetType() == Type.GetType("System.Array"))
                {
                    ndNewFields.InnerXml = @"<Method ID='1'>" +
                  "<Field Type='Text' DisplayName='" + fieldName + "' Required='FALSE' FromBaseType='FALSE' Description='Generated Field'/>" +
                  "</Method>";
                }
                else
                {   // Data type not handled - skip field
                    //Logger is my helper class - Remove from your code
                    Logger.LogMessageToFile("WARNING: Undefined field <" + fieldName + "> has an unhandled data type of: " + fieldValue.GetType().ToString() + "\r\n" +
                        "This field cannot be created");
                    canMigrateField = false;

                }
                Type myType = fieldValue.GetType();
                
                if (canCreateField)
                {
                    //NOTE: You need to use the GUID in the listname field.  I have this pre-populated in the listInfo.ListName class
                    XmlNode ndReturn =
                     listService.UpdateList(listInfo.listName,
                     null, ndNewFields, null, null,
                     ndVersion.Value);


             Logger.LogMessageToFile("NEW FIELD <" + fieldName + "> Created successfully.\r\n");
        }

    }

    catch (Exception ex)
    {
        Logger.LogMessageToFile("Message:\n" + ex.Message + "\nStackTrace:\n" + 
            ex.StackTrace);
        Logger.PopupMessage();
    }

    if (canCreateField) return true;
    else return false;
}