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
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
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
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
Quote from: dlmyers on 18 Oct 2011 12:31:16 PM
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/