0 Replies - 452 Views - Last Post: 13 June 2016 - 10:13 AM

#1 alapee  Icon User is offline

  • Stressed Out Programmer

Reputation: 214
  • View blog
  • Posts: 2,464
  • Joined: 24-October 13

SQL Retrieving XML Item from SQL Column

Posted 13 June 2016 - 10:13 AM

First I perused the SQL Lists and couldn't find something similar. So if this should be somewhere else please move it.

Background:
I am using Cognos 10.2.1 Data Content Warehouse which is in a MS SQL Database. Currently I am Running RazorSQL and SSMS 2014 to figure it out. The data is stored as an 'ntext' datatype which has XML coding in it. This my first time trying to pull data from an XML coding in SQL, so please bare with me.

Here is what I have so far:
Spoiler


I think I am missing something simple. The basic Idea is to pull the information that encapsulated by the <reportName> tag. This query pulls data, but all the data is null and I know that is not correct, so it must be something I am doing wrong. From what I can tell the root is report and the next is reportName but this information is at the bottom of the XML.

Here is the bottom portion of a sample:
Spoiler


Let me know what you think. This base idea will be used for several queries that I need to run to help audit our Cognos Reports.

Thanks in advance.

Is This A Good Question/Topic? 0
  • +

Page 1 of 1