Hello Geert
Thanks for the solution. It works perfectly.
Now I have another problem with the SQL query.
I have created a new SQL query in EA (search with SQL builder). When I run the following "code" in EA, I get the expected result.
When I try to run the "code" from Pyhton or with your Excel plugin (eaexcelexporter), I get the following error in EA:
"Error processing XML document: invalid or illegal XML character"
The problem is or starts with the following line:
'o_LO.Name = 'Logical attachment object (LO)'
Do you have a solution for this problem?
eaRep = win32com.client.Dispatch("EA.App").Repository # Verbindung zum Enterprise Architect-Modell herstellen
SearchTerm = "{09D06C03-1882-4226-A7E9-6D97DE70F2EC}"
### SQL Abfrage an EA
sqlQueryConnector = """
SELECT
o.Object_Type AS CLASSTYPE,
o.ea_guid AS GUID,
p_package5.Name AS p_package5,
p_package4.Name AS p_package4,
p_package3.Name AS p_package3,
p_package2.Name AS p_package2,
p_package1.Name AS p_package1,
o.Name,
o.Stereotype,
(
SELECT
COALESCE(GROUP_CONCAT(a_SBB.Name, ''), '')
FROM t_connector c_SBB
LEFT JOIN t_object o_SBB ON c_SBB.End_Object_ID = o_SBB.Object_ID
LEFT Join t_attribute a_SBB ON o_SBB.Object_ID = a_SBB.Object_ID AND o_SBB.Name = 'SBB-CFF-FFS'
WHERE c_SBB.Start_Object_ID = o.Object_ID AND c_SBB.Connector_Type = 'Association' AND o_SBB.Name = 'SBB-CFF-FFS'
) AS 'SBB-CFF-FFS',
(
SELECT
COALESCE(GROUP_CONCAT(a_LO.Name, ''), '')
FROM t_connector c_LO
LEFT JOIN t_object o_LO ON c_LO.End_Object_ID = o_LO.Object_ID
LEFT Join t_attribute a_LO ON o_LO.Object_ID = a_LO.Object_ID AND o_LO.Name = 'Logisches Anlagenobjekt (LO)'
WHERE c_LO.Start_Object_ID = o.Object_ID AND c_LO.Connector_Type = 'Association' AND o_LO.Name = 'Logisches Anlagenobjekt (LO)'
) AS 'Logisches_Anlagenobjekt_(LO)',
(
SELECT
COALESCE(GROUP_CONCAT(a_PO.Name, ''), '')
FROM t_connector c_PO
LEFT JOIN t_object o_PO ON c_PO.End_Object_ID = o_PO.Object_ID
LEFT Join t_attribute a_PO ON o_PO.Object_ID = a_PO.Object_ID AND o_PO.Name = 'Physisches Anlagenobjekt'
WHERE c_PO.Start_Object_ID = o.Object_ID AND c_PO.Connector_Type = 'Association' AND o_PO.Name = 'Physisches Anlagenobjekt'
) AS ['Physisches Anlagenobjekt'],
(
SELECT
COALESCE(GROUP_CONCAT(a_SPEZ.Name, ''), '')
FROM t_connector c_SPEZ
LEFT JOIN t_object o_SPEZ ON c_SPEZ.End_Object_ID = o_SPEZ.Object_ID
LEFT Join t_attribute a_SPEZ ON o_SPEZ.Object_ID = a_SPEZ.Object_ID AND o_SPEZ.Name = 'Spezifikation'
WHERE c_SPEZ.Start_Object_ID = o.Object_ID AND c_SPEZ.Connector_Type = 'Association' AND o_SPEZ.Name = 'Spezifikation'
) AS ['Spezifikation'],
(
SELECT
COALESCE(GROUP_CONCAT(a_OBJ_LO.Name, ''), '')
FROM t_connector c_OBJ_LO
LEFT JOIN t_object o_OBJ_LO ON c_OBJ_LO.End_Object_ID = o_OBJ_LO.Object_ID
LEFT Join t_attribute a_OBJ_LO ON o_OBJ_LO.Object_ID = a_OBJ_LO.Object_ID AND o_OBJ_LO.Name LIKE '%LO'
WHERE c_OBJ_LO.Start_Object_ID = o.Object_ID AND c_OBJ_LO.Connector_Type = 'Association' AND o_OBJ_LO.Name LIKE '%LO'
) AS ['OBJ_LO'],
(
SELECT
COALESCE(GROUP_CONCAT(a_OBJ_PO.Name, ''), '')
FROM t_connector c_OBJ_PO
LEFT JOIN t_object o_OBJ_PO ON c_OBJ_PO.End_Object_ID = o_OBJ_PO.Object_ID
LEFT Join t_attribute a_OBJ_PO ON o_OBJ_PO.Object_ID = a_OBJ_PO.Object_ID AND o_OBJ_PO.Name LIKE '%PO'
WHERE c_OBJ_PO.Start_Object_ID = o.Object_ID AND c_OBJ_PO.Connector_Type = 'Association' AND o_OBJ_PO.Name LIKE '%PO'
) AS ['OBJ_PO'],
(
SELECT
COALESCE(GROUP_CONCAT(a_OBJ_SPEZ.Name, ''), '')
FROM t_connector c_OBJ_SPEZ
LEFT JOIN t_object o_OBJ_SPEZ ON c_OBJ_SPEZ.End_Object_ID = o_OBJ_SPEZ.Object_ID
LEFT Join t_attribute a_OBJ_SPEZ ON o_OBJ_SPEZ.Object_ID = a_OBJ_SPEZ.Object_ID AND o_OBJ_SPEZ.Name LIKE '%SPEZ'
WHERE c_OBJ_SPEZ.Start_Object_ID = o.Object_ID AND c_OBJ_SPEZ.Connector_Type = 'Association' AND o_OBJ_SPEZ.Name LIKE '%SPEZ'
) AS ['OBJ_SPEZ']
FROM t_object o
Inner JOIN t_package p_package1 ON (o.Package_ID = p_package1.Package_ID)
Inner JOIN t_package p_package2 ON (p_package2.package_id = p_package1.Parent_ID)
Inner JOIN t_package p_package3 ON (p_package3.package_id = p_package2.Parent_ID)
Inner Join t_package p_package4 ON (p_package4.package_id = p_package3.Parent_ID)
Inner Join t_package p_package5 ON (p_package5.package_id = p_package4.Parent_ID)
WHERE p_package2.ea_GUID = '""" + SearchTerm + "' AND o.Stereotype = 'SBB_BIM_Data::fdkObjektdatentyp'"
print (sqlQueryConnector)
xmlResult = eaRep.SQLQuery(sqlQueryConnector)
from_xmlResult = XET.fromstring(xmlResult)
