Create Microsoft SQL Report (SSRS) based on XML data stored in database

Question

I'm trying to create a Microsoft SQL Report in Visual Studio 2012. My data input for the report is an XML binary file stored in a database.

To break it down, this is what I would like to do:

1. Retrieve the XML binary file from the database

2. Populate the report with the content of the XML

3. Create a PDF version of the report

Step 1 and 3 are not a problem. But step 2 is. I don't know how to use the XML data in a dataset so I use it in the report.

Is there something fundamental I'm missing?

I have created many reports before by using just data from a database but never from XML.

Thank you.

Answer 1

If the XML is stored in the DB and you can only access it through a SQL query, I'd suggest the following:

1. Create an XML datasource with an empty connection string. Normally it'd be a Web site/service URL.
2. In the report's dataset choose to use the XML data source. Note that the only possible command type is now Text.
3. Add an internal parameter for the report, which would contain your XML file.
4. Write an expression to get the default value for the parameter from the DB. The value should be your XML.

5. In the dataset command use XML Query to select the fields, for example:

   <Query>
      <XmlData>
       <!-- Your XML starts -->
       <Books>
           <Book id='1'>Foo</Book>
           <Book id='2'>Bar</Book>
           <Book id='3'>Baz</Book>
       </Books>
       <!-- Your XML ends -->
      </XmlData>
      <ElementPath>Books/Book</ElementPath>
   </Query>
       <!--just tweaked indentation-->
   

This will add two fields: id and Book. You need to use the parameter instead of the XML.

UPDATE: Sorry for XML formatting, I cannot get code block to work.