Geeks With Blogs
Clara's Notes on Technology Notes to me and the world

I have spent quite some time latetly creating two SQL Server Reporting Services reports that read data from a Web services. Given that I had to solve a couple of non-trivial issues and that I did not find much documentation, here are a few tips.

Tip 1: How to specify the value of the WS complex type parameter?

My Web service received some XML as input and returned some other XML.

To create the data source, just specify XML as type and the Web service URL as connection string. This is well documented (here and here).

However, I found almost nothing about how to pass the input information to a WS that uses complex parameters. By complex parameters, I mean that the WS signature look like this:

<soap:Body>
    <GetSomeXml xmlns="myreports.ws">
      <Root >
        <TP Name="string">
          <B Name="string" Surname="string">
            <SA xsi:nil="true" />
          </B>
          <B Name="string" Surname="string">
            <SA xsi:nil="true" />
          </B>
        </TP>
        <TP Name="string">
          <B Name="string" Surname="string">
            <SA xsi:nil="true" />
          </B>
          <B Name="string" Surname="string">
            <SA xsi:nil="true" />
          </B>
        </TP>
      </Root>
    </GetSomeXml>
  </soap:Body>

If you have the same situation, use a query such as the one below:

<Query>
   <SoapAction>mycompany.myreports/GetSomeXml</SoapAction>
    <Method Namespace="mycompany.myreports" Name="GetCodeAnalysisXml"></Method>
    <Parameters>
      <Parameter Name="Root" Type="XML"/>
    </Parameters>
    <ElementPath IgnoreNamespaces="True">
GetSomeXmlResponse{}/GetSomeXmlResult{}/TP{@Name}/B{@Name,@Surname}/SA{}/Issue{@Address,@Location,@Type,@Category}
    </ElementPath>
</Query>

The ElementPath tag should help you get the fields and attributs that you need from the WS response (in my Web Service the request and response use the same XML structure).

Notice the XML type of the Root parameter. Don't forget to specify the parameter in the dataset parameters tab (as Root) and give it some value.

When you run the query in the designer, a popup will ask you to specify a value. Just type some XML in.

 

Even with all this, I still had a problem for which I've never found a solution, only a workaround: the XML passed as parameter is escaped! The request to the Web service looks like this:

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetSomeXml xmlns="mycompany.myreports">
      <Root>&lt;TP Name="some name"&gt;&lt;B Name="foo" Surname="bar" /&gt;&lt;B Name="john" Surname="smith"
/&gt;&lt;/TP&gt;&lt;TP Name="test"&gt;&lt;B Name="sdef" Surname="fsdfs" /&gt;&lt;/TP&gt;</Root>
    </GetSomeXml>
  </soap:Body>
</soap:Envelope>

For me, the only way to get around this issue was to add a SOAP extension that converts the &lt; and &gt; to < and >. I would love to hear about any other solutions...

Tip 3: How to pass another dataset as  XML to the Web service?

The XML that I pass as input to the XML actually had to come from a database query. In order to achieve this I did the following

1) Create a dataset that contains the query to your database and add "FOR XML AUTO" at the end of the query [You can use any of the "FOR XML" variations in order to obtain the XML that you need]. This will return the query results in XML form and all in one field (with an auto-generated name such as XML_F52E2B61_18A1_11d1_B105_00805F49916B).

2) Create a report parameter (e.g. WSParam) that takes the value of this field as default

3) In the dataset that queries the Web service set as value of the parameter (Root in Tip 1) the value of WSParam.

Tip 2: How to read XML from a database without truncation of the results?

Just when everything seemed to work fine, I realized there was something wrong with my SQL XML query. If the results were large, they were truncated, so that I obtained two rows with a part of the XML in each! (I think the limit is at 2046 characters but I'm not sure).

Fortunately, there was a solution (found here - "Querying XML Data from SQL Server"). Your query should look like this:

DECLARE @x xml
SET @x = (SELECT
     TP.[Name] , 
     B.[Name],
     B.[Surname],
FROM
     [B_Table] as B, 
     [TP_Table] as TP
WHERE
     B.[TP] = TP.[__ID]
     AND TP.[Name] NOT LIKE 'Deleted' 
FOR XML AUTO

SELECT @x as WSInput

instead of

SELECT
     TP.[Name] , 
     B.[Name],
     B.[Surname],
FROM
     [B_Table] as B, 
     [TP_Table] as TP
WHERE
     B.[TP] = TP.[__ID]
     AND TP.[Name] NOT LIKE 'Deleted' 
FOR XML AUTO

Not only will you be able to choose a more friendly name for the field (e.g. WSInput) but the data is returned a an XML stream and therefore not truncated.

 

 

 

Posted on Friday, February 23, 2007 7:42 AM | Back to top


Comments on this post: Reporting Services and XML Data Sources

# re: Reporting Services and XML Data Sources
Requesting Gravatar...
Great article. I too am finding documentation concerning complex type parameters using the XML data provider for web services somewhat... lacking.

In my case, I need to pass a web service a parameter that looks something like this:

Blah type=container

within the container type you have

name
address
state

the web service is looking for something like

container.name=Matt

I have no idea how to get what is essentially an object to be passed as a parameter.
Left by Matt on Jun 22, 2008 6:56 PM

# re: Reporting Services and XML Data Sources Tip 2: How to read XML from a database without truncation of the results?
Requesting Gravatar...
Thank you!

This fixed a related problem in SQL Server Reporting Services where I was trying to populate a parameter with the results of an XML query.

Well done - keep up the good work!
Left by Ad the Lad on Dec 18, 2008 6:00 PM

# re: Reporting Services and XML Data Sources
Requesting Gravatar...
In my case, i have the XML generated in my code (For business reasons, i need to query the DB , get the data and manipulate and build the xml which contains the data to be displayed in the report) and i am not able to figure out a way to pass this xml to report... Can you please suggest?
Left by Satya on Jun 03, 2009 3:40 PM

# re: Reporting Services and XML Data Sources
Requesting Gravatar...
From the Visual Studio 2005 documentation:

"Query parameters of type Msxml2.DOMDocument30 are passed as XML. Parameters of type String which happen to contain XML are passed as strings and are XML encoded in the SOAP message. The function CXml(String) converts a string into an Msxml2.DOMDocument30 and can be used in query parameter expressions."
Left by a on Jun 18, 2009 2:59 PM

# re: Reporting Services and XML Data Sources
Requesting Gravatar...
Hi,

I need to pass an XML structure as argument to a web service. The XML message is as follows
<ProcessContext>
<UserId>
<FieldRef Name="UserId"/>
<Value Type="String">myName</Value>
</UserId>
</ProcessContext>
How to specify this as input parameter to the service, which i am using as XML data source for SSRS. I declared the parameter type as follows
<Parameter Name="processContext" Type="xml">
<DefaultValue>
</DefaultValue>
How to provide the above XML message as Value (or DefaultValue) for this parameter
Any help in this regard is greatfully appreciated

thanks & regards
dinesh kumar
Left by Dinesh Kumar on Feb 18, 2010 6:54 AM

# re: Reporting Services and XML Data Sources
Requesting Gravatar...
Very useful article! It resolved my issue of accessing the Informatica Powercenter WebService using MS Reporting Service.
Left by Silvio Sodré on Jul 01, 2011 5:18 PM

# re: Reporting Services and XML Data Sources
Requesting Gravatar...
Hi,
I have an issue parametrizing a report with and XML Data Source.
Most of the documentation I have found talk about XML coming from SOAP WebService. Nevertheless the data source that I have does not comes from an XML document.

The datasource has been established following this documentation: http://msdn.microsoft.com/en-us/library/aa964129.aspx

Now the report works if it is not parametrized, but when trying to parametrize it:
I use this query:

<Query>
<Parameters>
<Parameter Name="ceid" Type="XML">
<DefaultValue>/</DefaultValue>
</Parameter>
</Parameters>
<ElementPath IgnoreNamespaces="true">
Main{}/Item{}/ceid{}/entity{}/wwd{}/ww{}/interval_start_date{}/availability{}/magoal{}
</ElementPath>
</Query>

I have not established any Method as I am not sure it is required and I don't know which one to use.
When running the report it gives me this error:
An error occured during local report processing. An error occured during report processing. Query execution failed for dataset 'bla'. Failed to execute web request for the specified URL. Method not allowed: Thre remote server returned an error: (405) Method Not Allowed.........

Left by Georgina on Oct 05, 2011 5:47 PM

# re: Reporting Services and XML Data Sources
Requesting Gravatar...
Sorry... I could not finish my message. Your experience is very interesting, although it is based upon a web service source. I was wondering that maybe I have the same issue of paramaters being escaped, but don't know how to fix it.
I will appreciate your help on this.
Thanks in advance,
Left by Georgina on Oct 05, 2011 6:02 PM

# re: Reporting Services and XML Data Sources
Requesting Gravatar...
This is an old blog post, but in case someone else stumbles upon this as I did, I wanted to post my success in sending XML string to a service from SSRS. I too was getting xml tag's angle brackets converted to &lt; and &gt; by the SSRS's XML data provider. I am not sure if it is because I am using SSRS 2008 R2, but by using Type="XML" on the Parameter tag, the xml passed through to the service correctly, i.e., without the &lt; and &gt; conversion.

So I used a similar syntax that you used in your post:
<Parameter Name="Root" Type="XML"/>
and it work for me, at least in SSRS 2008.


Note: the attribute name Type is case sensitive, so type="xml" and TYPE="xml" will not work. Although the "xml" value used here is not case sensitive.
Left by Steve Vinge on Oct 17, 2011 7:58 AM

# re: Reporting Services and XML Data Sources
Requesting Gravatar...
How do I make SOAP extension that converts the &lt; and &gt; to < and >.
Left by Omer on Jun 02, 2014 8:01 AM

# re: Reporting Services and XML Data Sources
Requesting Gravatar...
Hi, I have a field in a web service that I have to use in a report, for this I created an xml connection with a URL that ends in .svc, can anyone tell me how I could perform the query? thank you very much
Left by eduardo on Jun 23, 2017 1:44 PM

Your comment:
 (will show your gravatar)


Copyright © Clara Oscura | Powered by: GeeksWithBlogs.net