One nice addition to SQL 2005 Reporting Services is the ability to have a XML Data Source (e.g. Web Service). However, getting it to work seems to be a little trickier than need be, especially for SharePoint’s web service.
Following the example in the SQL Books Online makes it look like you can apply it to just any web service but you’ll end up fighting with the parameters if you do as they simply don’t get properly serialized. After researching the issue and combining several different answers it comes down to the XmlDP Query syntax. The syntax which works looks like this (using GetList as an example):
The difference from the books online is the <Method> node which seems trigger something in RS to handle the issue. One other note is that even with the Method node, SSRS is still picky about the parameters so I have pretty much gotten by using only the essential parameters. I haven’t really built upon this example too much further but feel free to leave a comment if you have.
The following instructions assume an existing report but they should still work just fine using the add report Wizard.
- Add a new Data Source
- Set the Type to XML
- Set the Connection String to the URL of the web service:
- Use the param name from the WSDL
- Use a list name for the value
You should now have at least some data coming back. If you have any specific problems and/or fixes please feel free to post a comment.