How to do a String search and extract a value from an XML script

[sydazak]

Hi All

I have an XML field it's a CLOB data type which stores all the xml info. For my perpouses what I need to do is to extract values from the Middle of the start and end tags. Any suggestions on how i can perform that?    I tried using position but that would not be helpful because of the long script

Example of data:

<Class>G</Class>
<statusCode>716</statusCode>
<Partners>6</Partners>

Expected Results:
(field name)Class = G (value)
statusCode = 716
Partners = 6

so to summarize i want to extract data from the middle of the start and end tag.

[CognosPaul]

I'm assuming you're using Oracle.

Oracle has a number of XML specific SQL functions. For example, if you were writing hardcoded SQL, it might look like :

The XML snippet is incomplete, so I'll modify it to make it conform to standards in order to allow the functions to work:

Code Select Expand

<myData>
  <myRow>
    <Class>G</Class>
    <statusCode>716</statusCode>
    <Partners>6</Partners>
  </myRow>
</myData>


In this case you could use the following to get the 716
SELECT EXTRACTVALUE(xmltype(clobField), '/myData/myRow/statusCode')  FROM TABLE

The hard part then becomes using this in Cognos. Are you already pulling fields from this table? If so, you could try creating a data item that is something like:

Code Select Expand

{EXTRACTVALUE(xmltype(clobField), '/myData/myRow/statusCode') } The curly braces will instruct Cognos to pass the function directly to the DB. This would only work if you're already referencing the table since Cognos won't know to build the join to it otherwise.

[sydazak]

Hey paul, thanks for the response.

Yes i am using Oracle.Would it be a better idea to do this in FrameWork manager. If so how would you suggest about doing that.

Thanks again!