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.

 FlashListReader1

 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

                                                    });

                        }