John Luif

Today I ventured into the world of XML datasources in reporting services.  First off creating the XML datasource is easy but creating the Dataset from a query is another story.  Russle Christopher’s post provides a good starting point but there are some things that are not explained in his post.  The part I needed to know is how the Query and ElementPath work.  The xmlns in the Query works just how you would expect.  Define you namespaces and you can alias them so you can use them in ElementPaths.  The below is an example:

<Query xmlns:es="http://test.test.com”>
     <ElementPath>es:RetrieveInvoiceResponse{}/es:InvoiceInfo</ElementPath>
    <SoapAction>http://test.test.com/RetrieveInvoice</SoapAction>
</Query>

 In Russle’s post he talks about datasets (in the .NET sense), in my case I’m just serializing simple objects thus no diffgram or NewDataSet. 

Suppose you have xml for your datasource like the below as your datasource (NOTE:  I created a datasource directly off an xml doc).

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
    <RetrieveInvoiceResponse xmlns="http://test.test.com">
      <InvoiceInfo>
        <OrgId>2</OrgId>
        <Name>John</Name>
      </InvoiceInfo>
    </RetrieveInvoiceResponse>
  </soap:Body>
</soap:Envelope>

 Your Query and ElementPath would look like:

<Query xmlns:es="http://test.test.com" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <ElementPath>soap:Envelope {}/soap:Body{}/es:RetrieveInvoiceResponse{}/es:InvoiceInfo</ElementPath>
</Query>

Notice how the namespace aliases are used in the element path.  Another important piece to note is the “{}” in the ElementPath.  This is for defining columns to include.  If the “{}” are left off all columns are returned.  So the above query would return the columns “OrgId” and “Name”, but if I did this:

<Query xmlns:es="http://test.test.com" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <ElementPath>soap:Envelope {}/soap:Body{}/es:RetrieveInvoiceResponse{}/es:InvoiceInfo{Name}</ElementPath>
</Query>

Only the “Name” of InvoiceInfo would be returned.

I still have one thing I’m struggling with; if I leave off the “{}” because I want all columns returned my report dataset does not populate the columns (in the Datasets explorer) so I have to define each column right now for them to show up in my dataset.  Hopefully I can find a solution to this.