Answered

Help loading Entity from Excel using fromXmlToEntityWithScopes

Background: I would like to populate master entity records from
data residing in Excel using the instructions from http://help.bizagi.com/bpm-suite/en/read_and_write_from_excel.htm. To test this out, I created a very simple
process and data model that looks like this:
7fc4c8be93772d7ae74cbc54573e0b5b
During the execution of a process task, I would like to
import records from Excel into the Master Entity collection associated to my
Process Entity.



I used instructions from : http://help.bizagi.com/bpm-suite/en/bizagis_data_model_xml_schemas.htm
to generate the XSD for the master entity table and associate an XSLT that I
created externally.
c21761e36744e3b54d3882ad7f46ac79
I use a third party tool called Sparx EA to validate my
XSLT. The validation takes my input XML generated
by Excel, applies the XSLT transformation to create an output XML that is
validated against the XSD produced by Bizagi for my Master Entity table.



Input XML:

  1. <?xml version="1.0" standalone="yes"?>
  2. <NewDataSet>
  3. <Table>
  4. <id>1</id>
  5. <listvalue>BizagiXLS</listvalue>
  6. </Table>
  7. </NewDataSet>



xlsTest2Bizagi.xslt:

  1. <?xml version="1.0" encoding="utf-8"?>
  2. <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">;
  3. <xsl:output method="xml" indent="yes"/>

  4. <xsl:template match="/">
  5. <A1M_ChildRecord>
  6. <xsl:for-each select="NewDataSet/Table">
  7. <IDNumber>
  8. <xsl:value-of select="id"/>
  9. </IDNumber>
  10. <ListValue>
  11. <xsl:value-of select="listvalue"/>
  12. </ListValue>
  13. </xsl:for-each>
  14. </A1M_ChildRecord>
  15. </xsl:template>
  16. </xsl:stylesheet>



Output XML (Corresponds to Schema of target Master Entity):

  1. <A1M_ChildRecord><IDNumber>1</IDNumber><ListValue>BizagiXLS</ListValue></A1M_ChildRecord>




And Finally, this is the XSD for the target Master Entity that is generated by Bizagi:

  1. <?xml version="1.0" encoding="utf-8"?>
  2. <xs:schema attributeFormDefault="qualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">;
  3. <xs:element name="A1M_ChildRecord">
  4. <xs:complexType>
  5. <xs:sequence>
  6. <xs:element minOccurs="0" maxOccurs="1" name="IDNumber" type="xs:integer" />
  7. <xs:element minOccurs="0" maxOccurs="1" name="A1P_XLSTest" type="xs:integer" />
  8. <xs:element minOccurs="0" maxOccurs="1" name="ListValue" type="xs:string" />
  9. </xs:sequence>
  10. </xs:complexType>
  11. </xs:element>
  12. </xs:schema>

Before I try anything with .NET, I want to be successful with
the following simple case to simulate inserting just one record represented by
AML into my target master entity table.


To perform this test, I created a data entry form on which I
placed a button control that Executes an expression as follows:

  1. var xmlString = "<NewDataSet><Table><id>1</id><listvalue>BizagiXLS</listvalue></Table></NewDataSet>"
  2. var transformation = CHelper.getXSL("A1M_ChildRecord", "xlsTest2Bizagi");
  3. var xmlTransformed = CEntityXmlHelper.transformXmlAsString(xmlString,transformation);
  4. CEntityXmlHelper.fromXmlToEntityWithScopes(Me,xmlTransformed);



As you can see, I have the XML that would be produced
from Excel and returned by the .NET component hardwired into a variable for
now.


However, when I try to execute this code I receive the
following error message:


Error processing Rule = 7b80eb34-4e5b-47f1-b94a-7be567139bdb;
Error: Error Evaluating Rule RunXLSTest - Error Error saving XML: XPath =
A1M_ChildRecord. Relation not found. - bad xpath or Relation not found..


And nothing is inserted into my master entity table.


I think my XSLT might need to produce an output XML that
includes reference to the parent process entity, but at this point I am kind of
stuck. Does anyone out there have
experience with reading data from XML into Entities that can see where I might
be going wrong? Thanks in advance to
anyone who can provide some guidance for me. Let me know if I need to provide additional details.

Comments (4)

photo
1

Dear Jeffrey,

A member of our product team will contact you.

Regards

photo
1

It took some further trial and error, but a solution was finally achieved. I apologize for not coming back to update this thread with our successful approach, but here it is.

The master entity shown in my example is a child to the process entity. More specifically it is a on the many end of a one to many relationship with the process entity, and referenced from the process entity through a collection attribute that is defined on the process entity. The key to making this work is making sure the XSD schema is always defined on the process entity, and include the tree down through the master entity as shown here:
27a30878bbc08b33f8e25f58acdcd3e4
Notice that I didn't include the relationship field from the master back up to the parent.
Of course now I have an XSD with another level of hierarchy to it:

  • <?xml version="1.0" encoding="utf-8"?>
    1. <xs:schema attributeFormDefault="qualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">;
    2. <xs:element name="A1P_XLSTest">
    3. <xs:complexType>
    4. <xs:sequence>
    5. <xs:element minOccurs="0" maxOccurs="unbounded" name="ChildRecords">
    6. <xs:complexType>
    7. <xs:sequence>
    8. <xs:element minOccurs="0" maxOccurs="unbounded" name="A1M_ChildRecord">
    9. <xs:complexType>
    10. <xs:sequence>
    11. <xs:element minOccurs="0" maxOccurs="1" name="IDNumber" type="xs:integer" />
    12. <xs:element minOccurs="0" maxOccurs="1" name="ListValue" type="xs:string" />
    13. </xs:sequence>
    14. </xs:complexType>
    15. </xs:element>
    16. </xs:sequence>
    17. </xs:complexType>
    18. </xs:element>
    19. </xs:sequence>
    20. </xs:complexType>
    21. </xs:element>
    22. </xs:schema>



    so my xml structure will be different


    1. <?xml version=\"1.0\" standalone=\"yes\"?>
    2. <NewDataSet>
    3. <Table>
    4. <id>1</id>
    5. <listvalue>BizagiXLS</listvalue>
    6. </Table>
    7. <Table>
    8. <id>2</id>
    9. <listvalue>JeffP</listvalue>
    10. </Table>
    11. <Table>
    12. <id>3</id>
    13. <listvalue>Duke Energy</listvalue>
    14. </Table>
    15. </NewDataSet>




    and therefore the xslt has to adjust:


    1. <?xml version="1.0" encoding="utf-8"?>
    2. <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">;
    3. <xsl:output method="xml" indent="yes"/>

    4. <xsl:template match="/">
    5. <A1P_XLSTest>
    6. <ChildRecords>
    7. <xsl:for-each select="NewDataSet/Table">
    8. <A1M_ChildRecord>
    9. <IDNumber>
    10. <xsl:value-of select="id"/>
    11. </IDNumber>
    12. <ListValue>
    13. <xsl:value-of select="listvalue"/>
    14. </ListValue>
    15. </A1M_ChildRecord>
    16. </xsl:for-each>
    17. </ChildRecords>
    18. </A1P_XLSTest>
    19. </xsl:template>
    20. </xsl:stylesheet>



    but once I made these changes, the following code runs successful and forms the basis of successful import of xlsx into Bizagi.


    1. var xmlString = "<?xml version=\"1.0\" standalone=\"yes\"?><NewDataSet><Table><id>1</id><listvalue>BizagiXLS</listvalue></Table><Table><id>2</id><listvalue>JeffP</listvalue></Table><Table><id>3</id><listvalue>A Company Name</listvalue></Table></NewDataSet>"
    2. var transformation = CHelper.getXSL("A1P_XLSTest", "xlsTest2Bizagi");
    3. var xmlTransformed = CEntityXmlHelper.transformXmlAsString(xmlString,transformation);
    4. <A1P_XLSTest.TestComment>=xmlTransformed;
    5. CEntityXmlHelper.fromXmlToEntityWithScopes(Me,xmlTransformed);

    photo
    1

    One further thing to note. We found you must always start the XSD schema from the process entity, no matter how deep your structure. So if you have Process Entity that has an Entity (child) attribute, and then have a collection on that child entity that needs to be populated using this approach, you still need to define the XSD schema at the process entity, thus having 3 levels of structure. Hope that makes sense, and it may not be the best solution, but in case it worked.

    photo
    1

    Dear Jeffrey.

    Thank you for your comment. This kind of comments work very well to this community.

    We are glad that it had been resolved.

    Regards