Badly formed HTML in Aras FormattedText Fields

Question

I have the following scenario.
I have a report action bound to an item type which performs a REST call to the Microsoft SSRS Report Server with a delimited set of IDs.
The RDL Report performs an SQL query against a fixed ItemType to retrieve the meta-data for the given IDs.
Problem #1:&nbsp;
Several of the fields are of type FormattedText - although the relevant value may only be a few lines of text, Aras packs so much HTML and JavaScript around them that these Fields can exceed 100k characters.&nbsp; If generating an XLS report, there is a hard limit on the length of any given cell (32767 characters) - if this limit is exceeded, the report blows up with a HTTP Response Code error.
Potential Solution #1.
I wrote a simple SQL Function to convert the formatted text field string to XML and back again to a NVARCHAR(max) which effectively strips all XML elements from the text...the *magic* happens in this statement.
&nbsp; -- convert to XML and back to VARCHAR(max) again&nbsp;&nbsp;SET @result = CONVERT(XML, @result , 1).value('.', 'NVARCHAR(max)');
Problem #2:
In several data sets the FormattedText fields contain HTML string content which is not valid XML.&nbsp; &nbsp;In this case, the conversion function blows up.
Examples:

undefined namespace prefixes:&nbsp; '&lt;o:p&gt;...&lt;/o:p&gt;'&nbsp; (note: the prefix can vary)
invalid attribute definitions:&nbsp; '&lt;br \="" ... &gt;

This means I have to identify and correct each special case with time-consuming REPLACE(Column, Find, Replace) calls - a few of those on 100k+ character strings starts to blow up the query times.&nbsp; Only when the string is *clean* can I try to convert it to XML.&nbsp; For example,
&nbsp; SET @result = REPLACE(REPLACE(@result, '&lt;o:p','&lt;p'), '&lt;/o:p', '&lt;/p');&nbsp; SET @result = REPLACE(REPLACE(@result, '&lt;u5:p','&lt;p'), '&lt;/u5:p', '&lt;/p');&nbsp; SET @result = REPLACE(@result, '\=""', '');
I could go and fix each *special case* with one-time SQL scripts, but I cannot *guarantee* that Aras will not write new fields with invalid XML.So my question is can Aras take a look at the formatted fields and *guarantee* that they will no longer write poorly-formed XML(HTML).&nbsp; Maybe they could provide an out-of-the-box SQL functionality to make it possible properly prepare FormattedText fields for export.
NOTE: Maybe this has already been resolved - our current customer project is still using Aras 11 SP9.&nbsp; In that case, we just need to clean the old data and we are good to go.

christopher_gillis · Answer

Hi JWT007,
Are you seeing this behavior on standard ItemTypes? If so, could you specify which one(s), so I can take a look at this in a more current version to see if this is still present?
Chris

jwt007 · Answer

Hi Christopher, thanks for the quick response.
Unfortunately (if I am not mistaken) the only OOTB FormattedText property in the Aras InnovatorSolutions database is on the "Help" ItemType - which is empty by default.
So while I am sure I could fudge some broken data :) I am not sure that would prove anything.
In our customer project we have some item types that rely heavily on the FormattedText fields - but all in custom item-types.

christopher_gillis · Answer

I see. I set up a custom ItemType with a formatted text property then and tested this in a local 12.0 SP4 instance.&nbsp;While I'm not seeing anything unexpected, I'm also testing it with relatively simple data.
Are you able to elaborate on how the formatted text field is being used and what kind of data you're trying to retrieve?&nbsp;Are you trying to access a list of values stored inside of an HTML table for instance?
Chris

jwt007 · Answer

Nothing special some fields have a bold header .. few are more than a few lines long.Here an example (have to obfuscate a bit to not get in trouble with the customer :P)To be implemented by&nbsp;&nbsp;FOO&nbsp;BAR team for FOO-XYZ (ref. ABC_CFG_001-PTF Configuration management throught MMI).To be implemented by&nbsp;FOO L2 team for FOO-AAA.FOO-AAA is not operational yet and not handed over to OPS, but must be kept aligned to FOO-XYZ.&nbsp;
In the database is the following:
&lt;html&gt; &lt;head&gt;&lt;/head&gt; &lt;body &gt; &lt;span lang="EN-GB" style="mso-ansi-language:EN-GB"&gt;To be implemented by &lt;span style="mso-spacerun:yes"&gt; &lt;/span&gt;FOO BAR team for FOO-XYZ (ref. ABC_CFG_001-PTF Configuration management throught MMI).&lt;br /&gt;To be implemented by FOO L2 team for FOO-AAA.&lt;br /&gt;FOO-AAA is not operational yet and not handed over to OPS, but must be kept aligned to FOO-XYZ. &lt;o:p&gt;&lt;/o:p&gt;&lt;/span&gt;&lt;/body&gt; &lt;/html&gt;
In this case, the problem is the undefined namespace prefix 'o'&nbsp; ("&lt;o:p&gt;&lt;/o:p&gt;")

christopher_gillis · Answer

Thanks for the clarification. The trailing&nbsp;&lt;o:p&gt;&lt;/o:p&gt; at the end looks like a empty paragraph. I tried reproducing this in my 12.0 SP4 instance also trying to add the empty paragraph at the end, and I'm not seeing the same behavior. The HTML itself looks more condensed though this could be because I don't have multiple languages enabled in my environment. Additionally, it seems any trailing whitespace is trimmed from the field before being saved.
I'd argue your intuition was correct that this has already been addressed in more recent versions. It's difficult to say though. As you're more familiar with the exact data causing the issue, I'd be interested to see if you could reproduce this in a newer instance if you get the chance.
As a separate matter, I feel there might be better ways to store this data than a formatted text field. If the FOO BAR team or FOO-XYZ are actual items in the database, using a formatted text field limits the ability to actually link these Items where they're being referenced. Would it make more sense to store this data as a relationship? I could see this being a relationship called&nbsp;Implemented By that has an item property to store the Team doing the implementation, an item property to store who the implementation is being done for, and a text property to store any additional comments.&nbsp;As a customer project with existing data, I understand this might be out of your hands, but I could see it making life easier down the line. :)&nbsp;
Chris

Forum Discussion

Badly formed HTML in Aras FormattedText Fields

6 Replies

Recent Discussions

Tree Grid View slow: reads whole branch on expand — improvement ideas?

Auto expand x-properties

Does anyone see performance issues in Chrome

Aras 11SP9 Chrome remove XSLTProcessor

How to highlight a custom CUI Button like the Notification Button based on events of interest?