Querying XML data from SQL Server 2008 database

[dlmyers]

We have a SQL Server 2008 database that stores data from the application in XML. I would like to know about a tool, process or method that could be used to query the XML data in the database from Cognos 8

Thank you for your indulgence

[RobsWalker68]

Hi,

Have a look at using Cognos Virtual View Manager.

http://www.ibm.com/developerworks/data/library/techarticle/dm-1008xmlcognosvirtualview/?ca=drs-

Kind Regards


Rob

[dlmyers]

Rob,

Thank You for your response, it is appreciated.

In reviewing the document, It was not clear to me that the VVM was able to query the SQL Server database directly through an ODBC connection. It appeared that the data would need to be dumped to a "flat" XML file (datasource) that could then be consumed or queryed through the manager. If I am not understanding the supported types, I apologize and would appreciate any clarification.

Thank You again for your kind response.

D. Myers

[RobsWalker68]

Hi,

My understanding is that you are correct there doesn't appear to be away to access the XML directly held in SQL Server using VVM or Cognos natively.  You either have to use SQL Server itself to parse and write the XML to a table and then access that or access the an XML file.

Rgds


Rob

 

[the6campbells]

We have a SQL Server 2008 database that stores data from the application in XML. I would like to know about a tool, process or method that could be used to query the XML data in the database from Cognos 8

Thank you for your indulgence

While the ISO-SQL XML type is not directly supported nor the associated SQL/XML standard (local query execution etc) you can 'get there' manually in a report/FM by using the RDBMS vendors equivalents to XMLTable etc to expose the data in types and a form that can be accessed. Granted this is akin to shredding an normalizing relative to say a document centric XML column but.... Conceptually you would do similar as described in a DB2 paper - just look for your Vendor 'equiavlents'. The 'native' SQL could use #macros# etc to support parameters etc.

https://www.ibm.com/developerworks/data/library/techarticle/dm-0811saracco/