customize the fields returning from web service using ssrs xml query designer

Question

i have a web service that returns data but i cannot find a way how to choose the fields that returns from the web service. i have param-in that i send to the web service and i'm getting fields parameters as param-out. this is how i'm parsing it:

<Query>
<Method Name="methodname" Namespace="namespacename">
<Paramenters>
<Parameter Name="param-in-name-1">
<DefaultValue>0</DefaultValue>
</Parameter>
<Parameter Name="param-in-name-2">
<DefaultValue>0</DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespace="true">
*
</ElementPath>
</Query>

this parsing above brings all fields but i need to take specific fields. i tried to add this below to the elementpath but it didnt work: /elementname1{}/elementname2{}/fieldname1

Why not return all the fields and just use the ones you require in your reporting? — Jonnus, Nov 30 '15 at 17:24
hi, the issue is that the param out some are on first level and some are on second level(array of parameters) and i think this is why not all data are retrieved on the default option(*), some parameters that are on first level, then if there is param that have 2 levels i will get it too and then the rest of parameters on level 1 are not reaching — Y.D, Dec 03 '15 at 10:56

score 0 · Answer 1 · answered Dec 12 '15 at 13:49

I realized that its really depend on the web service schema for example, this schema that i made more complex by adding nested parameters:

<wsdl:types>
<s:schema targetNamespace="VS13" elementFormDefault="qualified">
<s:element name="datatable">
<s:complexType>
<s:sequence>
<s:element name="prmin" type="tns:paramin" maxOccurs="1" minOccurs="0"/>
</s:sequence>
</s:complexType>
</s:element>
<s:complexType name="paramin">
<s:sequence>
<s:element name="name" type="s:string" maxOccurs="1" minOccurs="0"/>
<s:element name="age" type="s:int" maxOccurs="1" minOccurs="1"/>
<s:element name="height" type="s:int" maxOccurs="1" minOccurs="1"/>
</s:sequence>
</s:complexType>
<s:element name="datatableResponse">
<s:complexType>
<s:sequence>
<s:element name="datatableResult" type="tns:data" maxOccurs="1" minOccurs="0"/>
</s:sequence>
</s:complexType>
</s:element>
<s:complexType name="data">
<s:sequence>
<s:element name="name" type="s:string" maxOccurs="1" minOccurs="0"/>
<s:element name="age" type="s:int" maxOccurs="1" minOccurs="1"/>
<s:element name="ltr" type="tns:ArrayOfLetters" maxOccurs="1" minOccurs="0"/>
<s:element name="height" type="s:int" maxOccurs="1" minOccurs="1"/>
</s:sequence>
</s:complexType>
<s:complexType name="ArrayOfLetters">
<s:sequence>
<s:element name="letters" type="tns:letters" maxOccurs="unbounded" minOccurs="0"/>
</s:sequence>
</s:complexType>
<s:complexType name="letters">
<s:sequence>
<s:element name="letter" type="s:string" maxOccurs="1" minOccurs="0"/>
<s:element name="count" type="s:int" maxOccurs="1" minOccurs="1"/>
</s:sequence>
</s:complexType>
</s:schema>
</wsdl:types>

i made it more complicated by sending parameters from the second layer(parameters inside a class/element):

<Query>
<Method Name="datatable" Namespace="VS13" >
<Parameters>
<Parameter Name="prmin" Type="xml">
<DefaultValue>
<name>stiv</name>
<age>30</age>
<height>180</height>
</DefaultValue>
</Parameter>
</Parameters>
</Method>

there are 2 ways for me to implement the data retrieved from the xmldp query

1.second layer without the array data field:

<ElementPath IgnoreNamespaces="True">
datatableResponse/datatableResult
</ElementPath>

2.second layer only the array data field:

<ElementPath IgnoreNamespaces="True">
datatableResponse/datatableResult{}/ltr/letters{letter,count}
</ElementPath>

customize the fields returning from web service using ssrs xml query designer

1 Answers1