Reporting Services and XML Data Sources

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.

• Share This Post:
• 
• 
•