SSRS - passing complex parameters to a webservice

Question

I am trying to get data from a webservice to put in a SSRS report.

Here is a dataset that works with a simple string parameter (The parameter is set in dataset properties):

<Query xmlns="http://tempuri.org/">
    <Method Name="ValidateUserWSToken" Namespace="http://tempuri.org/" />
    <SoapAction>http://tempuri.org/WSTokenService/ValidateUserWSToken</SoapAction>  
</Query>

Here is a similar method that expects a complextype for it's parameter

<Query>
    <Method Name="GetUserWSToken" Namespace="http://tempuri.org/"/>
    <SoapAction>http://tempuri.org/WSTokenService/GetUserWSToken</SoapAction>
</Query>

Putting the parameter in the dataset properties as

<UserName>username</UserName><Password>password</Password>

gives the error "There was an error trying to deserialize parameter http://tempuri.org/:request. The InnerException message was 'Error in line 5 position 20. Expected state 'Element'.. Encountered "text" with name '', namespace ''.'.". I don't think setting this in dataset properties will work.

I have tried variations on the following to try to put the parameter in the query text

<Query>
    <Method Name="GetUserWSToken" Namespace="http://tempuri.org/" />
    <SoapAction>http://tempuri.org/WSTokenService/GetUserWSToken</SoapAction>
    <Parameters>
        <Parameter Name="request" type="xml">
                <DefaultValue xmlns:a="some namespace">
                    <a:Password>password</a:Password>
                    <a:UserName>username</a:UserName>
                </DefaultValue>
        </Parameter>
    </Parameters>
</Query>

but these all fail with a 500 error - "Object reference not set to an instance of an object.", presumably because the webservice is not finding the parameter or finding it is null.

Here is the XML that SoapUI produces (and works)

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tem="http://tempuri.org/" xmlns:civ="some namespace">
   <soapenv:Header/>
   <soapenv:Body>
      <tem:GetUserWSToken>
         <!--Optional:-->
         <tem:request>
            <civ:Password>password</civ:Password>
            <civ:UserName>username</civ:UserName>
         </tem:request>
      </tem:GetUserWSToken>
   </soapenv:Body>
</soapenv:Envelope>

Can this be converted into something I can use in SSRS query text?

CooncilWorker CooncilWorker · Accepted Answer · 2019-09-12T13:57:19

This works:

<Query>  
    <Method Name="GetUserWSToken" Namespace="http://tempuri.org/">   
        <Parameters>
            <Parameter Name="request" Type="XML" xmlns="some namespace">
                    <DefaultValue>
                            <Password>password</Password>
                            <UserName>username</UserName>
                    </DefaultValue>
            </Parameter>
        </Parameters>
    </Method>
    <SoapAction>http://tempuri.org/WSTokenService/GetUserWSToken</SoapAction>
    <ElementPath IgnoreNamespaces="true">*</ElementPath>
</Query>

and this is what it is converted to:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetUserWSToken xmlns="http://tempuri.org/">
      <request><Password xmlns="some namespace">Password123</Password><UserName xmlns="some namespace">FifeIntegrationUser</UserName></request>
    </GetUserWSToken>
  </soap:Body>
</soap:Envelope>

SSRS - passing complex parameters to a webservice

1 Answers