Checking out Sharepoint webservices

During my work the last few weeks I've been working a lot with webservices and more specific Sharepoint webservices. Eventhough you can hardly call them webservices. They are a pain a real in the ass, the APIs are bad, but the worst thing is the MSDN documentation about them: absolutely useless.

The only thing that makes working with them bearable is soapUI. This excellent, multiplatform application makes life very easy when you need to explore the webservices some product/website/application offers. Just download the standalone version or use one of the various IDE plugins. After this it is as easy as

1. Create a new project via File > New soapUI Project
   
2. Enter a project name
3. Provide a WSDL, either via a local file or via a URL to a remote service
4. Keep the Create Requests checkbox ticked
   
5. Click OK

After this soapUI will generate a sample request for all the methods present in the WSDL. Now you just have to select the method you want to test, fill in some parameters (soapUI will indicate the optional ones) and click play. That's it.

The only additional functionality that I needed to use was provide some authentication credentials using the Aut button on the bottom of a request window.

One of the more useful Sharepoint webservices that I used was the List Data Retrieval service: http://host:port/_vti_bin/dspsts.asmx?wsdl. This has the ability to return the all the fields of a Sharepoint list together with a definition of the structure of the results. Below an example of the request I used:

  
     
        1.0
     
     
     
  
  
     
        
           
              
                 
              
           
        
      
  

Getting SSIS to like Sharepoint XMLs

I'm currently working on a project in which we need to get some content from a Sharepoint instance into a SQL server database. The work is being done by another consultant using Microsoft SSIS. The simplest way to get the data from one side to the other would be to do a straight database to database data synchronization. The problem with this plan is that the Sharepoint database schema just plain sucks and that Microsoft doesn't recommend accessing it directly, not just because of the crappy schema, but also because it could change between Sharepoint versions.

So plan B is to use the abundantly available Sharepoint webservices (if you could call them webservices). Since SSIS has support for webservices we tried that and failed again. We found no way to get SSIS to understand the Sharepoint webservices, it just kept complaining about the WSDL and the XSD. So Googling gave us an alternate solution: create some stubs around the webservice and use those in a script task. This got use a bit further but then SSIS started complaining about the XML using multiple namespaces and it also couldn't handle the XHTML content in some tags.

But again we found a workaround. Using the following XSLT to preprocess the XML before using it made SSIS accept it. The XSL removes all namespaces and places the XHTML content of selected tags, by means of a xsl:param in CDATA sections. One last problem was that SSIS doesn't give you the means to provide an XSL with params, so you'll have to put them directly into the content of the xsl:param.

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

 <xsl:output method="xml" /> 
 
 <xsl:param name="rtfNodes">TagsName1~TagsName1</xsl:param>

 <xsl:template match="*">
  <xsl:choose>
     <xsl:when test="contains($rtfNodes, local-name())">
       <xsl:element name="{local-name()}"> 
     <xsl:apply-templates select="@*" />
     <xsl:text disable-output-escaping="yes"><![CDATA[ <![CDATA[  ]]></xsl:text>
     <xsl:copy-of select="node()"/> 
     <xsl:text disable-output-escaping="yes"><![CDATA[]]]]><![CDATA[>]]></xsl:text>
    </xsl:element> 
     </xsl:when>
     <xsl:otherwise>
       <xsl:element name="{local-name()}">  
     <xsl:apply-templates select="@*" />    
     <xsl:apply-templates select="node()" />    
    </xsl:element>   
     </xsl:otherwise>
  </xsl:choose>  
 </xsl:template>    

 <xsl:template match="@*">    
  <xsl:attribute name="{local-name()}">    
   <xsl:value-of select="."/>    
  </xsl:attribute>    
 </xsl:template>    

 <xsl:template match="/">    
  <xsl:copy>    
   <xsl:apply-templates />    
  </xsl:copy>    
 </xsl:template>    

 <xsl:template match="comment() | processing-instruction() | text()">    
  <xsl:copy />    
 </xsl:template>  
 
</xsl:stylesheet>