select xml data column into flat reporting table

Question

We have a xml column in SQL Server 2008. We need to do reporting off the data in the xml so we're going to select the xml into a flat table. The flat table has columns that correspond to various nodes in the xml.

What is the best way to do this using SSIS?

Is this a good approach? Or should we just try and write the reports directly off the xml column?

Answer 1

I would look at using SSRS(SQL Server Reporting Services) It can query the XML Column by using xpath and write the report of the column directly.

more info here: http://msdn.microsoft.com/en-us/library/aa964129(SQL.90).aspx

Ignore the part about web services.