How to grab multiple parent/child elements from XML Data Source

We had a question come up about why an XML query wasn’t pulling a 2nd Parent/Child element from the resulting data.  This was grabbing data off of a Web Service.  The example I was given was a public Web Service call for getting Weather information.

https://wsf.cdyne.com/WeatherWS/Weather.asmx?op=GetCityForecastByZIP

The data that is returned looks like the following:

<ForecastReturn xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns="https://ws.cdyne.com/WeatherWS/">
<Success>true</Success>
<ResponseText>City Found</ResponseText>
<State>TX</State>
<City>Keller</City>
<WeatherStationCity>Euless</WeatherStationCity>
<ForecastResult>
<Forecast>
<Date>2014-04-22T00:00:00</Date>
<WeatherID>17</WeatherID>
<Desciption>Drizzle</Desciption>
<Temperatures>
<MorningLow>64</MorningLow>
<DaytimeHigh>89</DaytimeHigh>
</Temperatures>
<ProbabilityOfPrecipiation>
<Nighttime>20</Nighttime>
<Daytime>10</Daytime>
</ProbabilityOfPrecipiation>
</Forecast>

The problem we were seeing is that when we grab the initial query, we don’t see the values under ProbabilityOfPrecipiation (yes I know there is a typo there). 

We only see the items under Temperatures (MorningLow & DaytimeHigh).  The query that was being used is the following:

<Query>
<Method Name="GetCityForecastByZIP" Namespace="https://ws.cdyne.com/WeatherWS/">
<Parameters>
<Parameter Name="ZIP" type="string">
<DefaultValue>76244</DefaultValue>
</Parameter>
</Parameters>
</Method>
<SoapAction>
   https://ws.cdyne.com/WeatherWS/GetCityForecastByZIP
  </SoapAction>
<ElementPath IgnoreNamespaces="true">*</ElementPath>
</Query>

You’ll notice that ElementPath is using *.  Using * is the same as leaving ElementPath blank, and will cause the query to use the default element path.  The first path to a leaf node collection, which would be Temperatures in this case and be something like ForecastReturn/ForecastResult/Forecast/Temperatures.  As a result, we don’t see the values in ProbabilityOfPrecipiation.  The following MSDN document outlines this as well.  It’s with regards to RS 2005, but still applicable.

Reporting Services: Using XML and Web Service Data Sources
https://technet.microsoft.com/en-US/library/aa964129(v=SQL.90).aspx

With regards to Auto-Detection of XML Structure:
Multiple parent-child hierarchies are not supported. In this example, Customer has both Orders and Returns. The provider may return only one set. Because the Orders hierarchy is specified first, auto-derivation will resolve it as the skeleton.

With a single query, you can’t grab multiple Parent-Child elements.  You can grab it with a second query and then use the Lookup function to pull related data.  I created a second query with the following syntax:

<Query>
<Method Name="GetCityForecastByZIP" Namespace="https://ws.cdyne.com/WeatherWS/">
<Parameters>
<Parameter Name="ZIP" type="string">
<DefaultValue>76244</DefaultValue>
</Parameter>
</Parameters>
</Method>
<SoapAction>
   https://ws.cdyne.com/WeatherWS/GetCityForecastByZIP
  </SoapAction>
<ElementPath IgnoreNamespaces="true">GetCityForecastByZIPResponse{}/GetCityForecastByZIPResult{}/ForecastResult{}/Forecast/ProbabilityOfPrecipiation</ElementPath>
</Query>

You may be asking where ForecastReturn is, as that is the root element.  For the Web Service call, you need to put the headers for the actual method call itself, which is GetCityForecastByZIP.  As a result, we have to use GetCityForecastByZIPResponse and then GetCityForecastByZIPResult.  You’ll also notice the {}.  This is to indicate I don’t want the values from those elements.  This is what we see for the field list.

Now we have two queries and Date is the unique item here.  So, we can do a lookup on the Date across the two DataSets.  I’ll start the main table off of the first DataSet.  Then add two columns to the Table with the following Expressions:

Daytime Probability
=Lookup(Fields!Date.Value, Fields!Date.Value, Fields!Daytime.Value, "Precipitation")

Nighttime Probability
=Lookup(Fields!Date.Value, Fields!Date.Value, Fields!Nighttime.Value, "Precipitation")

The result is the output that we originally wanted.

Adam W. Saxton | Microsoft SQL Server Escalation Services
https://twitter.com/awsaxton