Extract text between HTML Tags - Report Studio

[Dobziski]

Hi, I need a congos report studio solution to extract text between html tags. For example the below is from a column in a SQL server DB table:

<font face="none" style=""><font color="#262626"><span style="font-size: 13px;"> Getting following error: message.ResponseEntry[0].Transaction.reasons.reason.code&nbsp; - When trying to active this account which denied initially due to missing info&nbsp;account.&nbsp;</span></font></font><div style=""><font color="#262626" face="none"><span style="font-size: 13px;">The customer wants to get a discount code.</span></font></div><div style="color: rgb(38, 38, 38); font-size: 13px;"><font face="none">Same issue we has on customer123 - and customer has called back for updates !</font></div>

I'm trying to apply either cognos functions or SQL server to extract the text between the tags. This should apply to any as the column is stored as "html" So the result would be the blow:

Getting following error: message.ResponseEntry[0].Transaction.reasons.reason.code&nbsp; - When trying to active this account which denied initially due to missing info;The customer wants to get a discount code.Same issue we has on customer123 - and customer has called back for updates !


Thank you!

[dougp]

Are you saying that the value with the tags is what is in your data, or are you trying to create something that scrapes the HTML that Cognos produces?

[MFGF]

Hi, I need a congos report studio solution to extract text between html tags. For example the below is from a column in a SQL server DB table:

<font face="none" style=""><font color="#262626"><span style="font-size: 13px;"> Getting following error: message.ResponseEntry[0].Transaction.reasons.reason.code&nbsp; - When trying to active this account which denied initially due to missing info&nbsp;account.&nbsp;</span></font></font><div style=""><font color="#262626" face="none"><span style="font-size: 13px;">The customer wants to get a discount code.</span></font></div><div style="color: rgb(38, 38, 38); font-size: 13px;"><font face="none">Same issue we has on customer123 - and customer has called back for updates !</font></div>

I'm trying to apply either cognos functions or SQL server to extract the text between the tags. This should apply to any as the column is stored as "html" So the result would be the blow:

Getting following error: message.ResponseEntry[0].Transaction.reasons.reason.code&nbsp; - When trying to active this account which denied initially due to missing info;The customer wants to get a discount code.Same issue we has on customer123 - and customer has called back for updates !


Thank you!

Hi,

A couple of questions:
1. Do you just want the text of the error message displayed in the report, or are you intending to use it further within other expressions?
2. Is the report rendering as just HTML or are you planning to render as other output types?

If you just want the text displayed, and you are just rendering the report as HTML, you could try bringing in an HTML item from the toolbox, then in properties of the item set the Source Type property to be 'Data item value' and the Data Item Value property to be the item containing the error message.

Cheers!

MF.

[Dobziski]

Are you saying that the value with the tags is what is in your data, or are you trying to create something that scrapes the HTML that Cognos produces?

Hi,

I need the values(text) within the tags. So basically strip/scrape the HTML from the column/field.

Thank you

[Dobziski]

Hi,

A couple of questions:
1. Do you just want the text of the error message displayed in the report, or are you intending to use it further within other expressions?
2. Is the report rendering as just HTML or are you planning to render as other output types?

If you just want the text displayed, and you are just rendering the report as HTML, you could try bringing in an HTML item from the toolbox, then in properties of the item set the Source Type property to be 'Data item value' and the Data Item Value property to be the item containing the error message.

Cheers!

MF.

Hi MF,

Thank you for your suggestion. I tried the second option and it worked. Unfortunately, this doesn't suffice as I need the report rendered or displayed in other output format (PDF & EXCEL). In response to question 1. Yes, that is what I need. To extract the text within the Html tags and display it in a list where the output is in excel and pdf.

Thank you!

[CognosPaul]

I would recommend using the SQL Server XML functions. First, cast the string to XML then use the XML query functions to extract the text data. I don't believe the XML functions are supported in Cognos, so you'll either need to write a UDF and import that or use curly brackets to force the function through.

I'll need to install SQL Server to test it, later this week.