Invoke a web service in SQL Server 2005 Reporting Services

To enable SQL Server 2005 Reporting Services reports to  query a web service a developer needs to know the web service namespace, method, SOAP Action, parameters, and schema of the response body. The XML data provider can query a web service directly by parsing the XML structure of the SOAP response directly. The XML Data Provider Query Language (as shown in the sample below), empowers a developer to build this query capability. Note that the language is syntactically and behaviourally different to XPATH.

Create an XML type of datasource and add the following to the query designer. Note that this query retrieves the node from the web service response below. To refer to from the report front-end simply use =Fields!data.Value;

XML Data Provider Query Language Sample <Query>     <Method Namespace=“http://localhost/TestWS/Service.asmx” Name=“HelloWorld“/>     <SoapAction>http://tempuri.org/HelloWorld</SoapAction>     <ElementPath IgnoreNamespaces=“True“>          HelloWorldResponse{}/HelloWorldResult{}/diffgram{}/TestDS{}/Table1{data}     </ElementPath> </Query> Web Service Response Sample <?xml version=“1.0“ encoding=“utf-8“?> <TestDS xmlns=“http://tempuri.org/“>   <xs:schema id=“TestDS“ targetNamespace=“http://tempuri.org/TestDS.xsd“ xmlns:mstns=“http://tempuri.org/TestDS.xsd“ xmlns=“http://tempuri.org/TestDS.xsd“ xmlns:xs=“http://www.w3.org/2001/XMLSchema“ xmlns:msdata=“urn:schemas-microsoft-com:xml-msdata“ attributeFormDefault=“qualified“ elementFormDefault=“qualified“>     <xs:element name=“TestDS“ msdata:IsDataSet=“true“ msdata:UseCurrentLocale=“true“>        <xs:complexType>          <xs:choice minOccurs=“0“ maxOccurs=“unbounded“>            <xs:element name=“Table1“>              <xs:complexType>                <xs:sequence>                  <xs:element name=“data“ type=“xs:string“ minOccurs=“0“ />                </xs:sequence>              </xs:complexType>          </xs:element>         </xs:choice>        </xs:complexType>     </xs:element> </xs:schema>   <diffgr:diffgram xmlns:msdata=“urn:schemas-microsoft-com:xml-msdata“ xmlns:diffgr=“urn:schemas-microsoft-com:xml-diffgram-v1“> <TestDS xmlns=“http://tempuri.org/TestDS.xsd“> <Table1 diffgr:id=“Table11“ msdata:rowOrder=“0“ diffgr:hasChanges=“inserted“>     <data>1</data> </Table1> <Table1 diffgr:id=“Table12“ msdata:rowOrder=“1“ diffgr:hasChanges=“inserted“>          <data>2</data> </Table1> </TestDS> </diffgr:diffgram> </TestDS>