Document Gen with Custom SQL Notes imbeds HTML

[Robert Kowalchuk]

I have a document fragment which uses the following custom SQL to find the elements I need to report on:

select t_object.Name as [Name], t_object.Note as [Note]
from t_object, t_connector, t_object as src_obj
where t_object.Package_ID = #PACKAGEID# and
            t_object.Object_ID = t_connector.Start_Object_ID AND
      t_connector.End_Object_ID = src_obj.Object_ID and
      src_obj.Name = 'Execute' and
      src_obj.StereoType = 'CapabilityCategory' and
      t_object.StereoType = 'Capability'

The problem arises when displaying the Note field.  It shows the content in the notes file literally and doesn't evaluate the formatting.  The RAW HTML is inserted into the generated document.  If I display the note field normally without a custom sql it display correctly.  Is there something special I need to do?

[Eve]

I don't think you can insert formatted text from a custom sql in the current version.

You will need to do something special when it becomes possible, but that documentation would be part of the release.

[Geert Bellekens]

You can rather easily solve that using a script fragment.

What you need to do is use Repository.SQLQuery() and pass the query you wrote to that operation.

The result of that operation is almost what you need. The only thing you need to add is the attribute "formatted=1"
From the manual:

If the text is a formatted Note, add  formatted="1" to the field row; for example: <Author formatted="1"><i>John</i></Author>
 

Geert

[Robert Kowalchuk]

Thanks for the help Geert, It is greatly appreciated.



Here are the snippet of the JScript for anyone who has similar issues.

Found out how to parse the query results into a DOM tree so I could set the attribute on the Notes.

Code: [Select]

function MyRtfData(parentName, objectID)
{
      
      var results = Repository.SQLQuery("select t_object.Name as [Name],  t_object.Note as [Notes] from t_object, t_connector, t_object as src_obj where t_object.Object_ID  = " + objectID + " and t_object.Object_ID = t_connector.Start_Object_ID AND t_connector.End_Object_ID = src_obj.Object_ID and src_obj.Name = '" + parentName + "' and src_obj.StereoType = 'CapabilityCategory' and t_object.StereoType = 'Capability'");
      //Session.Output(results);
      
      var xmlDOM = new ActiveXObject("MSXML2.DOMDocument.4.0");
      xmlDOM.loadXML(results);
      
      var notesXML = xmlDOM.getElementsByTagName("Notes");
      for( var i=0; i<notesXML.length;i++){
            notesXML.item(i).setAttribute("formatted","1");
      }
      
      return xmlDOM.xml;
}