CODEWITHSUNDEEP
sqlxmlcognos
user3017849
user3017849

Reputation: 93

Cognos parse xml - cognos side or sql side?

I have a view that pulls 3 columns from an audit table:

Col1        Col2     Col3
(String)    (Long)   (String)

Inside the long the long(Col2) I have XML:

<AuditDetail AuditType="TrackableItem">
    <Attributes>
        <Attribute DataType="class java.lang.Long" Name="Lockid"
            NewValue="11" OldValue="12"/>
        <Attribute DataType="class java.lang.String" Name="OverrideCode"
            NewValue="8899" OldValue="9998"/>
    </Attributes>
</AuditDetail>

I need my cognos side to display 4 columns (Col1 Col2 Col3 Col4), two columns will be columns that the view already returns, the other two need to be values selected from the xml (AuditDetail/Attributes/Attribute/newvalue)

How would I select the two data items from these xml files? would I parse somehow on the cognos side, or is there anyway to get the data values in the view?

Using oracle db.

Upvotes: 1

Views: 968

Answers (1)

Andrew
Andrew

Reputation: 8758

Since it's stored as a string, it really isn't going to make much difference. You're going to use the same kind of string functions (instr, substr, etc) in Cognos or the view. And it will be a huge pain. Doing it in a view would only force you to do it once, at least. Here's an example to help you get started:

SQLFiddle

select
substr (col1
       ,(instr(col1,'NewValue="')+10)  -- This gets you to the end of the NewValue=" string
      ,(instr(col1,'NewValue="')+10) -  
          instr(col1,'=',(instr(col1,'NewValue="'))) --This gets you the length, the second value is the first occurence of '"' AFTER your start position
        )
from
AuditData

Upvotes: 2

Related Questions