SharePoint, querying lists using DspSts, and consuming this information in Flash

UPDATE: See this blog post on the crossdomain.xml configuration file you have to put on your SharePoint server to allow Flash to access the SharePoint server using web services.

Summary

This blog entry describes how to do complex SharePoint web service calls from a Flash client application to retrieve arbitrary information from SharePoint lists. The very powerful web service DspSts.asmx is used to accomplish this task. This web service allows you to dynamically query any field and item selection from a list and apply ordering on this selection. It is also possible to restrict the number of items to return. The query applied is in the CAML query format.

Introduction

In my search for a possible new implementation of the MacawDiscussionBoard for SharePoint I’m looking into Flash as possible UI for SharePoint discussion lists. In this implementation it is key that all information sent to retrieved from SharePoint must be accomplished using the SharePoint web services interfaces. I’m using Flex 2.0 Alpha release to generate my flash application (Flash 8.5), but the techniques displayed here are for a large part also applicable for Flash 8 (the current release).

Problem I had was that I wanted to get list items not through the simple SharePoint web service interface Lists.asmx that provides a set of simple remote methos calls, but through the web service DspSts.asmx that requires a complete XML document to be passed, and has special additional SOAP headers.

Reason for using this more complex DspSts.asmx web service: the GetListItems(listName, viewName) call in Lists.asmx is too restrictive:

• It can only return fields of list items as defined in a predefined list view
• It returns all list items in the list view

The DspSts.asmx web service is way more powerful:

• Build queries dynamically in the CAML query format
• Query any field from the list
• Query a selection of items from the lis
• Apply ordering on this selection
• Restrict the number of items to return
• Get paged sets of items

Using the  DspSts.asmx web service with an XML document based on a complex schema defined in the WSDL and additional SOAP headers was way more complex than I thought it would be. I couldn’t find much information on this and was really struggling until I found the following article on the IBM site: Develop Web services clients with Macromedia Flex. It still took me really a lot of time to solve the ‘puzzle’, that is why I thought I better write this down for others trying to go the same route.

I moved my code into a simple test application where you can specify the URL of a SharePoint site containing a discussion list, and the ID of this list (Use SharePoint Explorer to get the ID). The application retrieves all list items from the discussion list and displays them in a grid. Only a small set of fields is retrieved… the set of fields needed to create a hierachical structure of all discussion list items. See below for an example of this application.

 The code below is in MXML (Th Flex markup language), a really powerful language for defining Flash applications. The application executes Query operation of the DspSts web service when the “Execute Query” button is clicked. Variables between ‘{’ and ‘}’ characters are data bindings. The result of the web service call is returned in XML, in the e4x format, the intrinsic XML format of ActionScript 3.

<?xml version="1.0" encoding="utf-8"?>

<mx:Application xmlns:mx="http://www.macromedia.com/2005/mxml" xmlns="*">

      <mx:Script>

      <![CDATA[

            import mx.rpc.events.*;

            import mx.rpc.soap.*;

            import mx.controls.Alert;

            import mx.controls.gridclasses.*;

            import mx.collections.*;

            [Bindable] public var siteURL:String="https://MySharePointPortalServer/personal/serge";

            [Bindable] public var listID :String="{020013c8-5efc-49a8-b28f-339d401c1046}";

            [Bindable] public var dataGridItems:IList = new ArrayCollection();

      ]]>

      </mx:Script>

      <mx:Canvas width="638" height="414">

            <mx:Label x="11" y="14" text="SharePoint Site URL:"/>

            <mx:Label x="12" y="44" text="List GUID (use \{..\}):"/>

            <mx:TextInput x="138" y="12" width="375" id="siteURL_textinput" text="{siteURL}"/>

            <mx:TextInput x="138" y="42" width="375" id="siteID_textinput" text="{listID}"/>

            <mx:Button x="521" y="43" label="Execute Query" click="wssDspStsService.Query.send()"/>

            <mx:DataGrid id="listRowsOutput" height="325" dataProvider="{dataGridItems}">

                  <mx:layoutConstraints>

                        <mx:EdgeAnchor left="13" right="13" bottom="13"/>

                  </mx:layoutConstraints>

                  <mx:columns>

                        <mx:DataGridColumn headerText="Ordering" columnName="Ordering" />

                        <mx:DataGridColumn headerText="ThreadID" columnName="ThreadID"/>

                  <mx:DataGridColumn headerText="ID" columnName="ID"/>

                  <mx:DataGridColumn headerText="Title" columnName="Title"/>

                  <mx:DataGridColumn headerText="Author" columnName="Author"/>

                  <mx:DataGridColumn headerText="Created" columnName="Created"/>

                  </mx:columns>

            </mx:DataGrid>

      </mx:Canvas>

<!-- For more information on format: See the WSS SDK, and search for "DspSts". Select the topic "Query Method".

       Online documentation: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/spptsdk/html/soapmqueryRequest_SV01071735.asp

-->

<mx:WebService

    id="wssDspStsService"

    wsdl="{siteURL}/_vti_bin/DspSts.asmx?wsdl"

    service="StsAdapter"

    port="StsAdapterSoap"

    useProxy="false"

    showBusyCursor="true"

    fault="Alert.show('Failed to load the DWSL. Error: ' + event.fault.faultstring)" load="wssDspStsService_AddHeaders()">

        <mx:operation name="Query" concurrency="single" resultFormat="e4x" fault="wssDspStsService_fault(event)" result="wssDspStsService_result(event)">

                  <mx:request xmlns="http://schemas.microsoft.com/sharepoint/dsp">

                    <queryRequest>

                      <dsQuery select="/list[@id='{listID}']" resultContent="dataOnly" resultRoot="Rows" resultRow="Row" columnMapping="attribute">

                          <Query QueryType="DSPQ">

                              <Fields>

                                  <Field Name="Ordering"/>

                                  <Field Name="ThreadID"/>

                                  <Field Name="ID"/>

                                  <Field Name="Title"/>

                                  <Field Name="Author"/>

                                  <Field Name="Created"/>

                              </Fields>

                              <OrderBy>

                                  <OrderField Name="Ordering" Type="xsd:string" Direction="DESC"/>

                              </OrderBy>

                          </Query>

                      </dsQuery>

                    </queryRequest>

                  </mx:request>

        </mx:operation>

    </mx:WebService>

    <mx:Script>

      <![CDATA[

            // We need to generate the following SOAP headers:

            //

            // <soap:Header xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

            //    <dsp:versions xmlns:dsp="http://schemas.microsoft.com/sharepoint/dsp">

            //        <dsp:version>1.0</dsp:version>

            //    </dsp:versions>

            //    <dsp:request xmlns:dsp="http://schemas.microsoft.com/sharepoint/dsp" service="DspSts" document="content" method="query">

            //    </dsp:request>

            // </soap:Header>   

            //

            // Only issue is that I can't service, document and method as attributes of request, but only as child elements. But it seems to work!

            // On the other hand: it already works if only the request header is there, ot does not matter about its attributes.

            private function wssDspStsService_AddHeaders()

            {

                  trace("Add HEADERS");

                  wssDspStsService.Query.addSimpleHeader("versions", "http://schemas.microsoft.com/sharepoint/dsp", "version", "1.0");

                  var qName: QName = new QName("http://schemas.microsoft.com/sharepoint/dsp", "request");

                  var requestHeader: SOAPHeader = new SOAPHeader(qName, {service:"DspSts",document:"content",method:"query"});

                  wssDspStsService.Query.addHeader(requestHeader);

            }

                  private function wssDspStsService_fault(event: FaultEvent)

                  {

                        Alert.show("Failed to execute the query. Error: " + event.fault.faultstring);

                  }

                  // Returned XML is in followin  format

                  // <queryResponse xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/sharepoint/dsp" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

                  //   <dsQueryResponse status="success">

                  //     <Rows>

                  //       <Row Created="2005-10-23T14:03:20" ThreadID="{20051023-1203-12E1-8E71-1F4426DE71D5}" ID="1" Title="Disc1" Ordering="20051023140320" Author="Serge van den Oever"/>

                  //       <Row Created="2005-10-23T14:03:32" ThreadID="{20051023-1203-2649-92C4-FA7641C9F5F7}" ID="2" Title="Disc2" Ordering="20051023140332" Author="Serge van den Oever"/>

                  //       <Row Created="2005-10-23T14:03:45" ThreadID="{20051023-1203-2649-92C4-FA7641C9F5F7}" ID="3" Title="Disc2" Ordering="2005102314033220051023140345" Author="Serge van den Oever"/>

                  //     </Rows>

                  //   </dsQueryResponse>

                  // </queryResponse>;

                  private function wssDspStsService_result(event:ResultEvent)

                  {

                        var queryResultXML:XML = wssDspStsService.operations.Query.result[0];

                        var dsp:Namespace = queryResultXML.namespace();

                        queryResultXML.setNamespace(dsp);

                        var queryResultRows:XMLList = queryResultXML..Row;

                        trace("#rows=" + queryResultRows.length());

                        dataGridItems.removeAll();

                        for each(var item:XML in queryResultRows)

                        {

                              dataGridItems.addItem({

                                                       Created: item.@Created,

                                                       ThreadID: item.@ThreadID,

                                                       ID: item.@ID,

                                                       Ordering: item.@Ordering,

                                                       Title: item.@Title,

                                                       Author: item.@Author

                                                    });

                        }

                  }

      ]]>

    </mx:Script>

</mx:Application>

The format of data returned from the DspSts web service

One of the nice and easy things of Flash is that you can have the results of a web service call return as an Object. This object can have fields of any type, hierarchical structures with fields of type object, and arrays of any type (also of type object). Problem is that the converter from the SOAP result to the object does its own interpretation of types, it does not check the schema data that can be returned in a SOAP result, you see an small part of this schema below:

I got into trouble with the Ordering field in a discussion list item. This field contains an ordering value in the format YYYYMMDDHHMMSS, for example 20051020140312. This field is interpreted as a field on type number in the conversion to an object. This is NOT what we want, because a reply on this list item gets an Ordering field with the original Ordering field value, with the timestamp of the reply appended. This string can become really long, does not fit in a number, and as a number is useless for ordering.

I found that it is also possible to return the result as XML, or e4x, the intrinsic XML format of ActionScript 3. The downside is that I have to interpret all returned XML data manually (it is all seen as strings), but the upside is that we don’t need to return the schema data in the result, which can become really large, because all users are returned  as a restriction enumertation for the Author field. This means that all users that ever visited the site containing the discussion list (and are therefore registered in the UserInfo table for this site) are returned in a list. No problem for a site only used by a few users, but a huge problem if you have a large user base. 

Things I couldn’t accomplish in the WebService implementation of Flash/Flex 2

During my adventures with WebServices in Flash/Flex 2 I couldn’t accomplish the following things:

Complex SOAP headers

I needed to create SOAPheader with the following format:

<dsp:request xmlns:dsp="http://schemas.microsoft.com/sharepoint/dsp" service="DspSts" document="content" method="query">

What ever I tried, the closest I could get was:

<dsp:request xmlns:dsp="http://schemas.microsoft.com/sharepoint/dsp">
<service>DspSts</service>
<document>content</document>
<method>query</method>
</dsp:request>