Sparx EA文件型模型报表<memo>长字段丢失问题求助

在基于MS Access的文件型Sparx Enterprise Architect中，使用自定义模板和以下SQL查询生成报表时，当<memo>字段内容超过249字符，会触发报错：Error Processing xml document: an invalid character was found in text context，对应单元格显示为空。

原SQL查询：

SELECT 
    obj.object_id,
    obj.Stereotype,
    objp.Property as Prop,
    switch(objp.Value = '<memo>', objp.Notes, objp.Value LIKE '{*}', 
           NULL, 1=1, objp.Value) AS Val,
    (SELECT tobj2.ea_guid & tobj2.Name 
     FROM t_object tobj2 
     WHERE tobj2.ea_guid = objp.Value) AS [Obj-Hyperlink]
FROM 
    t_object obj 
INNER JOIN 
    t_objectproperties objp ON (obj.object_id = objp.object_id)
WHERE 
    obj.object_id = #OBJECTID# 
    AND obj.Stereotype = 'Data-Stream' 
    AND objp.Property NOT IN ('isEncapsulated')
ORDER BY 
    objp.Property ASC;

问题根源 #

EA报表引擎基于XML解析生成内容，Memo字段中的XML敏感字符（如&、<、>、"）未转义，或超长文本超出默认解析阈值，导致XML结构失效，触发报错。

解决方法 #

1. 转义XML特殊字符

修改SQL中处理objp.Notes的逻辑，用Access的Replace函数依次转义所有XML敏感字符，确保XML解析正常：
将原SQL中objp.Notes替换为：

Replace(Replace(Replace(Replace(objp.Notes, "&", "&amp;"), "<", "&lt;"), ">", "&gt;"), """", "&quot;")

2. 处理超长文本（可选）

若转义后仍存在超长文本兼容问题，可添加长度判断，截取部分内容并提示完整内容位置：

IIf(
    Len(objp.Notes) > 1000, 
    Left(objp.Notes, 1000) & "...[完整内容请查看模型对象]", 
    Replace(Replace(Replace(Replace(objp.Notes, "&", "&amp;"), "<", "&lt;"), ">", "&gt;"), """", "&quot;")
)

3. 调整报表模板设置

在EA报表模板的对应单元格中，确保使用<text>标签包裹动态内容，或开启模板的HTML兼容模式（模板编辑器中设置），让EA能正确渲染长文本内容。

修改后的完整SQL #

SELECT 
    obj.object_id,
    obj.Stereotype,
    objp.Property as Prop,
    switch(
        objp.Value = '<memo>', 
        Replace(Replace(Replace(Replace(objp.Notes, "&", "&amp;"), "<", "&lt;"), ">", "&gt;"), """", "&quot;"), 
        objp.Value LIKE '{*}', 
        NULL, 
        1=1, 
        objp.Value
    ) AS Val,
    (SELECT tobj2.ea_guid &amp; tobj2.Name 
     FROM t_object tobj2 
     WHERE tobj2.ea_guid = objp.Value) AS [Obj-Hyperlink]
FROM 
    t_object obj 
INNER JOIN 
    t_objectproperties objp ON (obj.object_id = objp.object_id)
WHERE 
    obj.object_id = #OBJECTID# 
    AND obj.Stereotype = 'Data-Stream' 
    AND objp.Property NOT IN ('isEncapsulated')
ORDER BY 
    objp.Property ASC;

内容的提问来源于stack exchange，提问作者vascobnunes