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

Question

I am trying to create a Microsoft SQL report in Visual Studio 2012. My report data is a binary XML file stored in a database.

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

• Get binary XML file from database

• Fill the report with XML content

• Create a PDF version of the report

Step 1 and 3 are not a problem. But there is a step 2. I do not know how to use XML data in a dataset, so I use it in a report.

Is there something fundamental that I'm missing?

I have created many reports before, using only data from a database, but never from XML.

Thanks.

Answer 1

If XML is stored in the database and you can access it only through an SQL query, I would suggest the following:

• Create an XML data source with an empty connection string. This is usually the website / service URL.
• In the report dataset, select using an XML data source. Note that the only possible type of command is Text.
• Add an internal parameter for the report that will contain your XML file.
• Write an expression to get the default value for the parameter from the database. The value must be your XML.

• In the dataset command, use XML Query to select 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 XML.

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