Author Topic: Querying XML data from SQL Server 2008 database  (Read 4104 times)

Offline dlmyers

  • Associate
  • **
  • Join Date: Oct 2011
  • Posts: 2
  • Forum Citizenship: +0/-0
Querying XML data from SQL Server 2008 database
« 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

Offline RobsWalker68

  • Statesman
  • ******
  • Join Date: Apr 2009
  • Posts: 299
  • Forum Citizenship: +28/-1
    • Linkedin Profile
Re: Querying XML data from SQL Server 2008 database
« Reply #1 on: 19 Oct 2011 06:12:33 am »
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


Offline dlmyers

  • Associate
  • **
  • Join Date: Oct 2011
  • Posts: 2
  • Forum Citizenship: +0/-0
Re: Querying XML data from SQL Server 2008 database
« Reply #2 on: 19 Oct 2011 10:01:45 am »
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

Offline RobsWalker68

  • Statesman
  • ******
  • Join Date: Apr 2009
  • Posts: 299
  • Forum Citizenship: +28/-1
    • Linkedin Profile
Re: Querying XML data from SQL Server 2008 database
« Reply #3 on: 19 Oct 2011 10:24:53 am »
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

 

Offline the6campbells

  • Community Leader
  • *****
  • Join Date: Jan 2012
  • Posts: 88
  • Forum Citizenship: +6/-0
Re: Querying XML data from SQL Server 2008 database
« Reply #4 on: 31 Mar 2012 10:46:11 am »
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/



 


       
Twittear