Adding Multiple Items to an Existing List in SharePoint Team Services
Microsoft Corporation
June 2002
Applies to:
SharePoint™ Team Services from Microsoft
Microsoft XML Core Services (MSXML) 4.0
Summary: This article explores ways to make use of the Collaborative Application Markup Language (CAML) and MSXML with SharePoint Team Services to construct dynamically the post string for adding multiple items to a list programmatically. After walking through the steps for building a form, developers can peruse the complete VBScript code sample that loads data from a local spreadsheet as well as learn how to construct a Batch string for posting the new data to the server. (15 printed pages)
Contents
Introduction
Adding a Link and Processing Instructions
Adding a Form and a UI
Getting the View Fields
Loading and Posting the Data
Complete Code Sample
Introduction
When using SharePoint™ Team Services from Microsoft®, the Collaborative Application Markup Language (CAML) Batch element makes it possible to post more than one command at a time to the server. It can be used, for example, if you want to add a number of items to an already existing list through the post body of a simple form.
One way to do it is implement the CAML Batch manager. The following example adds two items to a list, which involves two methods, each with its own set of commands, parameters, and values embedded within CAML SetVar elements (The example below has been "broken" to fit within MSDN Web page layout requirements) :
Note You must have Advanced Author permissions on the server to upload the samples and perform the required customizations presented in this article.
<ows:Batch OnError="Return">
<Method ID="A1">
<SetList>u_MyCustomList</SetList>
<SetVar Name="ID">New</SetVar>
<SetVar Name="Cmd">Save</SetVar>
<SetVar Name="urn:schemas-microsoft-
com:office:office#Title">Cooking</SetVar>
<SetVar Name="urn:schemas-microsoft-
com:office:office#Writer">Morson</SetVar>
<SetVar Name="urn:schemas-microsoft-
com:office:office#Stock">20</SetVar>
<SetVar Name="urn:schemas-microsoft-
com:office:office#Price">12.95</SetVar>
</Method>
<Method ID="A2">
<SetList>u_MyCustomList</SetList>
<SetVar Name="ID">New</SetVar>
<SetVar Name="Cmd">Save</SetVar>
<SetVar Name="urn:schemas-microsoft-
com:office:office#Title">Bicycles</SetVar>
<SetVar Name="urn:schemas-microsoft-
com:office:office#Writer">Jones</SetVar>
<SetVar Name="urn:schemas-microsoft-
com:office:office#Stock">32</SetVar>
<SetVar Name="urn:schemas-microsoft-
com:office:office#Price">8.50</SetVar>
</Method>
</ows:Batch>
Obviously, using a form to add many new items soon becomes tedious, since a separate method must be typed for each new item.
This article explores how to dynamically construct the post string for adding multiple items to a list programmatically. It shows how to generate a string that wraps the command and parameters for each new item to be posted within a single Method element that has a unique ID. All the methods for all the new items are then wrapped within a single Batch element for posting.
The article also includes instructions for creating a form, including processing instructions for the SharePoint Team Services interpreter DLL, as well as how to link to the page from the AllItems.htm page of a list. The article explains how to use the XMLHTTP and DOMDocument objects to post the remote procedure call (RPC) ExportList command in order to return names of the list's view fields (i.e., the fields that are displayed in a list) for processing in script or CAML. It also shows how to load data from a local spreadsheet and how to construct a Batch string for posting the new data to the server.
Running the sample
The complete code sample described in this article assumes that the data source is a local spreadsheet, which has column headers and the same alignment of fields as those displayed in the list. Date and time values must be in ISO8601 format: YYYY-MM-DDTHH:MM:SSZ.
Note The example in this article assumes that you have installed the Microsoft XML Core Services (MSXML) 4.0, formerly called the Microsoft XML Parser, on the client. Download the Core Services.
In addition, since the script below instantiates a Microsoft Excel Worksheet object on the client, the security setting for Initialize and script ActiveX controls not marked as safe in Microsoft Internet Explorer (Tools menu, Options, command, Security tab) must be set to Enable or Prompt for the code to function.
**Important **This security change is only required because Excel is used in the following example, and users should be warned that leaving this Internet Explorer setting to a setting other than Disabled may leave your system vulnerable to malicious Microsoft ActiveX® controls on other Web pages.
Adding a Link and Processing Instructions
On the AllItems.htm page of a list, you can add a link like the following:
<a href='../../_layouts/AddItems.htm?List=<ows:List/>&View=<ows:GetVar Name="Using" URLEncode="TRUE"/>'>Add Items</a>
**Note **As a best practice, any use of the GetVar tag should use some form of encoding appropriate to the context – HTMLEncode, URLEncode, UrlEncodeAsUrl, or ScriptQuote.
The List=<ows:List/> parameter passes the current list name as part of the URL, which means that the list name will not have to be hard-coded on the AddItems.htm page. The View=<ows:GetVar Name="Using" URLEncode="TRUE"/> parameter passes the current list's view page, using the CAML GetVar element to return the URL and make the assignment. To make the link appear on the right side of the page, above Modify settings and columns, a table cell like the following can be added just after the cell defining the description area of the page (indicated by class="ms-descriptiontext"): <td class=ms-toolbar align=right><a href='../../_layouts/AddItems.htm?List=<ows:List/>&View=<ows:GetVar Name="Using" URLEncode="TRUE"/>'>Add Items</a></td>
Create a new page for adding items (here, AddItems.htm), and add the following instructions, which are necessary in order for the compiler to process the CAML on AddItems.htm.
The <HEAD> section on the page needs to contain the following script block, which allows relative links in the document to work correctly when viewed through the CAML interpreter DLL:
<Script Language="JavaScript">
document.write('<ows:HTMLBase/>');
var strBinUrl="../_vti_bin/";
</Script>
The <HEAD> section also needs to contain the following block, which allows for processing of the page by the SharePoint Team Services DLL:
<Script SRC="../_vti_bin/owsredir.js">
</Script>
<Script Language="JavaScript">
RedirectToFrame("%5flayouts%2fAddItems%2ehtm","../_vti_bin/owssvr.dll");
</Script>
Note You need to set the correct path and file name that are passed as the value of the first parameter to the RedirectToFrame function.
Adding a Form and a UI
On AddItems.htm, add a form that implements the SharePoint Team Services RPC method DisplayPost. Just as with the above link that you added to the list's AllItems.htm page, you can use CAML to dynamically complete the form's URL string (This code example has been "broken" to fit within MSDN Web layout requirements):
<FORM id=frmAdd method="POST"
action="<ows:HttpVDir/>/_vti_bin/owssvr.dll?Cmd=DisplayPost">
<INPUT type=hidden name="NextUsing" value="">
<INPUT type=hidden name="PostBody" value="">
</FORM>
In this case, <ows:HttpVDir/> specifies the path to the current subweb, which means that this form will work on any subweb.
You can then add text and a UI to the page, as in the following example. Note that, once again, CAML is used to make the page modular. Here, <ows:ListProperty Select='DefaultViewUrl' /> specifies the URL for the default view page (i.e., the page used in the list's default view) if the user decides to return to the original page. (This code example has been "broken" to fit within MSDN Web layout requirements):
<TABLE class="ms-toolbar" cellpadding=5>
<TR><TD><A href="<ows:ListProperty Select='DefaultViewUrl' />">Go back
to list</A></TD></TR>
<TR><TD colspan=2 class=ms-sectionheader>Add Items</TD></TR>
<TR><TD>Type the full path to the spreadsheet or click <B>Browse</B>
to find the file.</TD></TR>
<TR><TD><INPUT id=pathSheet Type="file" Name="pathSheet"
style="behavior: url(#SpreadsheetLauncherObj);"></TD></TR>
<TR><TD><a href="VBScript:AddListItems()" target=_self>Add
Items</a></TD></TR>
</TABLE>
The <INPUT> element, which contains style="behavior: url(#SpreadsheetLauncherObj), provides a Browse button and file picker for defining a path to the data source. The href attribute for the command to add items makes a call to the opening script function—AddListItems.
Getting the View Fields
The SharePoint Team Services RPC ExportList method can be used to get the list's view fields, which are required for construction of the Batch string to add items. When specified as the command for a given URL string, this method returns the entire list schema as an XML file, including a ViewFields section such as the following:
<ViewFields>
<FieldRef Name="LinkTitle"/>
<FieldRef Name="Writer"/>
<FieldRef Name="Stock"/>
<FieldRef Name="Price"/>
</ViewFields>
To return the list's view fields for manipulation in script, you can implement the ExportList method within a script block in the <HEAD> section to return the list's schema. First, the string to post is constructed:
<SCRIPT language="VBScript">
Dim getList, objDoc, objHTTP, ndCntr, viewNodes, PropNames(), strID, h
getList = "<ows:HttpVDir/>/_vti_bin/owssvr.dll?Cmd=ExportList" & _
"&List=<ows:List/>"
Then, to post this URL and get the view fields from the list schema that is returned, you can use XMLHTTP and DOMDocument objects:
Set objDoc = CreateObject("Msxml2.DOMDocument.4.0")
Set objHTTP = CreateObject("Msxml2.XMLHTTP.4.0")
objHTTP.open "POST", getList, false
objHTTP.send
Set objDoc = objHTTP.responseXML
objDoc.setProperty "SelectionLanguage", "XPath"
Path = "//View[@Url='<ows:GetVar Name="View" HTMLEncode="TRUE"/>']" & _
"/ViewFields/FieldRef"
Set viewNodes = objDoc.selectNodes(Path)
ndCntr = viewNodes.length
The async parameter is set to false so that the script won't continue to execute until the server has processed the request and provided a response. The response is assigned to objDoc, the DOMDocument object, which is used to fetch the view field names. The second parameter of the above setProperty method specifies that XPath is the language used in the subsequent XML selectNodes method, which selects nodes matching the XPath description. Notice that CAML is used to complete the XPath string, returning the view's URL that was passed from the originating AllItems.htm. The complete string specifies all View elements that have ViewFields/FieldRef subelements and whose Url attribute equals the URL. The collection of nodes returned is assigned to the viewNodes variable, and ndCntr contains the number of items returned.
At this point, the propNames variable is re-dimensioned as an array of the length specified by the ndCntr variable. This variable determines the number of iterations made in the following loop for returning each of the Name attribute values for the above FieldRef elements.
ReDim propNames(ndCntr)
For h = 0 To (ndCntr - 1) Step 1
strID = viewNodes.item(h).attributes.item(0).value
If (strID = "LinkTitle") Then
strID = "Title"
End If
propNames(h) = strID
Next
Notice that an If clause has been added in order to specify the Title field whenever LinkTitle shows up as one of the fields. This is done because the LinkTitle field uses the Title field for its value.
Loading and Posting the Data
Within the same script block in the <HEAD> section, place a script function that can be called when the user clicks Add Items. This function both initiates and closes processing through a series of functions that are used to construct the post string.
Function AddListItems()
Dim strPath, dfltUrl
strPath = pathSheet.value
If (strPath = "") Then
MsgBox("Enter the path to a data source.")
Exit function
End If
strReadyToPost = BuildPost(strPath)
dfltUrl = "<ows:ListProperty Select='DefaultViewUrl'/>"
frmAdd.NextUsing.value = dfltUrl
frmAdd.PostBody.value = strReadyToPost
frmAdd.submit()
End Function
The AddListItems function first assigns the path specified by the user to the srcPath variable. Error handling has been added in case no path is entered in the UI text box when the button is clicked. The AddListItems function calls the BuildPost function, which builds the string to post, and then assigns the value returned from BuildPost to a variable that is in turn assigned to the form's post body. Note that CAML is used once again to return the user to the default view page of the list once the post is completed.
After variables are declared in the following BuildPost function, a Microsoft Excel Application object is created and the workbook specified by the srcPath variable is opened:
Function BuildPost(srcPath)
Dim strBatch, strBuildPost, mthItms, strMeths
Dim srcApp, srcBk, srcRng, srcRows, srcColCnt, srcRowCnt, intID
Dim strReg, strPost, strReadyToPost, strMeth, lstItem, i, j, k, l
Set srcApp = CreateObject("Excel.Application")
Set srcBk = srcApp.Workbooks.Open(srcPath)
Set srcRng = srcBk.Worksheets(1).UsedRange
Set srcRows = srcRng.Rows
srcColCnt = srcRng.Columns.Count
srcRowCnt = srcRng.Rows.Count
k = 1
strPost = ""
The srcColCnt and srcRowCnt variables are used in iterating through the above propNames array and creating the commands for registering each value in the Microsoft Office namespace. A counter variable k is initialized, which is used below to create unique IDs for each method. The strPost variable is also initialized, which is used to gather up each method returned from the ProcessMethod function.
A command string must be created for each column value in the data source. The following code loops through each value and associates it with the appropriate field name contained in the propNames array. The i and j counter variables are used to iterate through all the rows and all the columns of the spreadsheet, and the l counter is used to iterate through each of the propNames elements.
For i = 2 To srcRowCnt
l = 0
strReg = ""
For j = 1 To srcColCnt
lstItem = srcRows(i).Cells(j)
strReg = strReg & "<SetVar Name=" & Chr(34) & _
"<ows:FieldPrefix/>" & propNames(l) & _
Chr(34) & ">" & lstItem & "</SetVar>"
l = l + 1
Next
strMeth = ProcessMethod(strReg, k)
k = k + 1
strPost = strPost & strMeth
Next
Notice that at this point angle brackets have been encoded in order to disguise CAML from the interpreter, which would otherwise immediately attempt to process the CAML when the page is opened. The FieldPrefix element, however, is not disguised, and specifies the string required for registering fields and values in the Office namespace (for example, "urn:schemas-microsoft-com:office:office#").
Once all the commands for a row have been built, the entire string and the counter (k) for creating unique method IDs are passed to the ProcessMethod function, which wraps the set of commands for each row within a Method element.
Once all the methods for each row in the original worksheet have been constructed, a call is made to the ProcessBatch function (see below), which wraps all the methods in a single Batch element. Before the BuildPost function concludes execution, angle brackets are replaced in the string for the post.
strBatch = ProcessBatch(strPost)
strBuildPost = Replace(strBatch, "<", "<")
BuildPost = Replace(strBuildPost, ">", ">")
srcBk.Close
Set srcApp = Nothing
End Function
The BuildPost function thus concludes by freeing up the memory used by the Excel Application and Workbook objects. The functions for wrapping the strings within Method and Batch elements are laid out as follows:
Function ProcessMethod(mthItms, intID)
strMeth = "<Method ID=" & Chr(34) & "AdLst" & intID & Chr(34) & _
"><SetList><ows:List/></SetList><SetVar Name=" & _
Chr(34) & "ID" & Chr(34) & _
">New</SetVar><SetVar Name=" & _
Chr(34) & "Cmd" & Chr(34) & ">Save</SetVar>"
strMeth = strMeth & mthItms
strMeth = strMeth & "</Method>"
ProcessMethod = strMeth
End Function
Function ProcessBatch(strMeths)
strBatch = "<ows:Batch OnError=" & Chr(34) & "Return" & _
Chr(34) & ">"
strBatch = strBatch & strMeths
strBatch = strBatch & "</ows:Batch>"
ProcessBatch = strBatch
End Function
</SCRIPT>
Once you've created the new file for adding items and have linked to it from a list's AllItems.htm page, the new file can be placed within the /_layouts folder of any existing subweb. If you want this file to be included in subwebs that will be created in the future, you can add it to the \Program Files\Common Files\Microsoft Shared\web server extensions\50\Templates\1033 \Layouts folder.
Complete Code Sample
The following sample can be placed in the <HEAD> section of the AddItems.htm page:
<SCRIPT language="VBScript">
'Create an RPC string to post through XML HTTP in
'order to export the list schema,and instantiate a
'DOM Document object to contain and parse the server's response.
'Use DOM to retrieve the view fields as a collection.
Dim getList, objDoc, objHTTP, ndCntr, viewNodes, PropNames(), strID, h
getList = "<ows:HttpVDir/>/_vti_bin/owssvr.dll?Cmd=ExportList" & _
"&List=<ows:List/>"
Set objDoc = CreateObject("Msxml2.DOMDocument.4.0")
Set objHTTP = CreateObject("Msxml2.XMLHTTP.4.0")
objHTTP.open "POST", getList, false
objHTTP.send
Set objDoc = objHTTP.responseXML
objDoc.setProperty "SelectionLanguage", "XPath"
Path = "//View[@Url='<ows:GetVar Name="View" HTMLEncode="TRUE"/>']" & _
"/ViewFields/FieldRef"
Set viewNodes = objDoc.selectNodes(Path)
ndCntr = viewNodes.length
'Create an array to contain the view fields.
ReDim propNames(ndCntr)
For h = 0 To (ndCntr - 1) Step 1
strID = viewNodes.item(h).attributes.item(0).value
If (strID = "LinkTitle") Then
strID = "Title"
End If
propNames(h) = strID
Next
'The following function calls the BuildPost function to
'retrieve the finished string and make the post.
Function AddListItems()
Dim strPath, dfltUrl
strPath = pathSheet.value
If (strPath = "") Then
MsgBox("Enter the path to a data source.")
Exit function
End If
strReadyToPost = BuildPost(strPath)
dfltUrl = "<ows:ListProperty Select='DefaultViewUrl'/>"
frmAdd.NextUsing.value = dfltUrl
frmAdd.PostBody.value = strReadyToPost
frmAdd.submit()
End Function
Function BuildPost(srcPath)
Dim strBatch, strBuildPost, mthItms, strMeths
Dim srcApp, srcBk, srcRng, srcRows, srcColCnt, srcRowCnt, intID
Dim strReg, strPost, strReadyToPost, strMeth, lstItem, i, j, k, l
'Instantiate an Excel application and retrieve
'the sheet's used range.
Set srcApp = CreateObject("Excel.Application")
Set srcBk = srcApp.Workbooks.Open(srcPath)
Set srcRng = srcBk.Worksheets(1).UsedRange
Set srcRows = srcRng.Rows
srcColCnt = srcRng.Columns.Count
srcRowCnt = srcRng.Rows.Count
'Iterate through the field names array and create
'the commands for registering each value in the Office
'namespace. Disguise CAML elements from the interpreter.
k = 1
strPost = ""
For i = 2 To srcRowCnt
l = 0
strReg = ""
For j = 1 To srcColCnt
lstItem = srcRows(i).Cells(j)
strReg = strReg & "<SetVar Name=" & Chr(34) & _
"<ows:FieldPrefix/>" & propNames(l) & _
Chr(34) & ">" & lstItem & "</SetVar>"
l = l + 1
Next
strMeth = ProcessMethod(strReg, k)
k = k + 1
strPost = strPost & strMeth
Next
strBatch = ProcessBatch(strPost)
'Replace HTML encoding with < and >.
strBuildPost = Replace(strBatch, "<", "<")
BuildPost = Replace(strBuildPost, ">", ">")
srcBk.Close
Set srcApp = Nothing
End Function
'The following function constructs a Method element with a unique ID
'to contain the above string of commands for each item (row).
Function ProcessMethod(mthItms, intID)
strMeth = "<Method ID=" & Chr(34) & "AdLst" & intID & Chr(34) & _
"><SetList><ows:List/></SetList><SetVar Name=" & _
Chr(34) & "ID" & Chr(34) & _
">New</SetVar><SetVar Name=" & _
Chr(34) & "Cmd" & Chr(34) & ">Save</SetVar>"
strMeth = strMeth & mthItms
strMeth = strMeth & "</Method>"
ProcessMethod = strMeth
End Function
'Construct the Batch element for containing
'the string of methods to post.
Function ProcessBatch(strMeths)
strBatch = "<ows:Batch OnError=" & Chr(34) & "Return" & _
Chr(34) & ">"
strBatch = strBatch & strMeths
strBatch = strBatch & "</ows:Batch>"
ProcessBatch = strBatch
End Function
</SCRIPT>